Variables in T-SQL behave differently from what people may be used to in languages like C or C#. The scope of a variable is the batch in which it is declared. This may not be intuitive to people who are used to local variables. So let’s look at an interesting example:<\/p>\n
DECLARE @counter INT<\/p>\n
SET @counter = 0<\/p>\n
WHILE @counter < 100<\/p>\n
BEGIN<\/p>\n
DECLARE @value INT<\/p>\n
SET @value = ISNULL(@value, 0) + 5<\/p>\n
SET @counter = @counter + 1<\/p>\n
END<\/p>\n
SELECT @value<\/p>\n
In this example we have a simple loop. The loop counter is a variable @counter. What is interesting that inside the loop we have a variable declaraion for @variable. We assign it a value (if it was null we set it to 5, if it was not null, we increment it by 5). Does this make sense? How could I ever consider the previous value if I’ve just declared the variable? What is unusual for many developers, is that the declaration is evaluated only once. This means that in the second iteration of the loop the @value variable is already declared and set, so we are incrementing it by five in every subsequent iteration. <\/p>\n
At the end of the loop we can access the @value variable (do not forget that the scope is the whole batch), and the value will be 500 (100 iterations, 100 times adding 5).<\/p>\n
Variable declarations are evaluated at the time of parsing, and the scope is the whole batch. Because of this, you cannot have two declare statements for the same variable in a batch, even if the only one declaration is reachable in the code. For example, the following statement will result in a parsing error:<\/p>\n
IF 1 = 1 <\/p>\n
DECLARE @a INT<\/p>\n
ELSE <\/p>\n
DECLARE @a INT<\/p>\n
The error is:<\/p>\n
Msg 134, Level 15, State 1, Line 4<\/p>\n
The variable name ‘@a’ has already been declared. Variable names must be unique within a query batch or stored procedure.<\/p>\n