Local variables and select statements

When you declare a variable, it has the value NULL. Assign values to local variables with a select statement.

As with declare statements, it is more efficient to use:

select @a = 1, @b = 2, @c = 3

than:

select @a = 1 
select @b = 2 
select @c = 3 

See the Reference Manual: Commands.

Do not use a single select statement to assign a value to one variable and then to another whose value is based on the first. Doing so can yield unpredictable results. For example, the following queries both try to find the value of @c2. The first query yields NULL, while the second query yields the correct answer, 0.033333:

/* this is wrong*/
declare @c1 float, @c2 float
select @c1 = 1000/1000, @c2 = @c1/30
select @c1, @c2
 
/* do it this way */ 
declare @c1 float, @c2 float
select @c1 = 1000/1000
select @c2 = @c1/30
select @c1 , @c2

You cannot use a select statement that assigns values to variables to also return data to the user. The first select statement in the following example assigns the maximum price to the local variable @veryhigh; the second select statement is needed to display the value:

declare @veryhigh money 
select @veryhigh = max(price) 
   from titles 
select @veryhigh 

If the select statement that assigns values to a variable returns more than one value, the last value that is returned is assigned to the variable. The following query assigns the variable the last value returned by “select advance from titles.”

declare @m money
select @m = advance from titles
select @m

(18 rows affected)
------------------------ 
                8,000.00 
 
(1 row affected)

The assignment statement indicates how many rows were affected (returned) by the select statement.

If a select statement that assigns values to a variable fails to return any values, the variable is left unchanged by the statement.

Local variables can be used as arguments to print or raiserror.