Other Uses for SELECT

3 4

The SELECT statement is used mainly to perform queries that retrieve data from the database. These queries can be simple or complex. SELECT can also be used to set values for variables or to call functions.

You can use the SELECT statement to set a value for a local variable within a transaction or a stored procedure, although the preferred method is to use the SET statement. Local variables must have an "at" symbol (@) as the first character. For example, to set the local variable @count to the value 0 by using SET, use the following statement:

  SET  @count = 0 GO 

You can use SELECT to set a local variable to the value returned from a query. For example, to set the local variable @price to the maximum value in the price column of the items table, use the following statement:

  SELECT   @price = MAX(price) FROM     items GO 

When you set a local variable within a SELECT statement, it's best if the statement returns one row for the query. If multiple rows are returned, the local variable will be given the value from the last row returned.

You can also use SELECT to call a function, such as the system-provided function GETDATE. The following statement calls GETDATE to retrieve the current date and time:

  SELECT   GETDATE() GO 

GETDATE does not take any parameters, but the parentheses are still necessary.