antony-688446 (4/30/2013)
For those struggling with the difference between 'False' and 'Unknown', and how the IF statement works, consider what happens if we change the example code a little bit (I think this could have added a couple of wrong answers to the results 🙂 )
DECLARE @ToCreate bit;
IF @ToCreate <> 1
DECLARE @Table TABLE
(id int, name varchar(50) )
ELSE
INSERT INTO @Table (id, name)
select 1, 'a';
SELECT * FROM @Table;
It now looks like the DECLARE should be executed, as @ToCreate is clearly not 1, but it isn't, as the comparison evaluates to 'Unknown'. This forces the ELSE part of the IF statement.
Hope this helps to clarify why Tom's whinge on BOL is important!
And, on the subject of missing information on BOL, I couldn't find any reference to when variables are created on the link provided...
Actually, DECLARE is executed.
It's executed in both version of the batch - original from the question and your, modifies one.
It will be executed even if you set @ToCreate to "non-executable" value:
DECLARE @ToCreate bit;
SET @ToCreate = 1
IF @ToCreate <> 1
DECLARE @Table TABLE
(id int, name varchar(50) )
ELSE
INSERT INTO @Table (id, name)
select 1, 'a';
SELECT * FROM @Table;
The whole point of the question was - DECLARE is executed regardless of the flow control logic in the code.
Any outcome of IF check - TRUE, FALSE or UNKNOWN - won't affect execution of DECLARE.
The comment would be correct if we put any other "run-time" command in place of DECLARE - SET, SELECT, etc.
ELSE is executed only when IF returns not TRUE (FALSE or NULL).
_____________
Code for TallyGenerator