see the other example around initialising a variable as part of the DECLARE. The statement is passed, and the variable is created, but the statement itself is NOT executed
That example is actually very good to prove the point but may be very confusing because of the "shortcut" syntax.
if @k = 0 begin declare @j int = 2 end
Since @k = 1 the block "begin-end" is not executed.
Wrong. Otherwise variable @j would not be declared and we would get "must declare variable @j" error further in the code.
So, it is executed. Right?
Wrong. Because @j does not get initialised with value 2.
What's the... ?
Explanation is simple.
There are actually 2 statements combined together:
declare @j int set @j = 2
Now it all becomes clear:
- DECLARE is executed during compilation, outside of IF..THEN control;
- SET is executed during run time, under IF..THEN control.
Actually, this syntax is one of the worst and most dangerous things introduced by MS in SQL2008.
I saw extremely powerful servers brought down by improper use of this syntax.
Not just tables, not databases. Whole servers, with losing ability to connect to master database.
I probably need to write an article about that experience, if nobody did it before.