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:
DECLARE @counter INT
SET @counter =
0
WHILE @counter <
100
BEGIN
DECLARE @value INT
SET @value = ISNULL(@value, 0) + 5
SET @counter =
@counter + 1
END
SELECT
@value
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.
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).
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:
IF 1 = 1
DECLARE @a INT
ELSE
DECLARE @a INT
The error is:
Msg 134, Level 15, State 1, Line 4
The variable name '@a' has already been declared. Variable
names must be unique within a query batch or stored procedure.
If you would like to know more about how transient data,
like the above variable, is handled in SQL Server, come to my session at SQL
Bits in Birmingham (UK) on 1 March, 2008. Like the previous SQL Bits event it
is free. The last one was held in Reading half a year ago, and with 20 sessions
and over 300 people attending it, it provides a unique opportunity to learn
about SQL Server, as well as to meet your fellow DBAs and SQL developers in the
UK. I will talk about transient data in SQL Server, and you can learn about
what is stored in tempdb, how the tempdb can be a performance bottleneck, what the
differences between temporary tables and table variables are, what is stored in
the transaction log file and how its can size be kept under control.