When you declare a variable, it has the value NULL. Use a select statement to assign values to local variables.
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.