Good question; many people would expect this to work. (As I did, the first time I ran into this. And the second, third, ... - I answered correctly but only because I've run into this often enough to cause a permanent dent in my forehead).
deepak.a (11/17/2010)
Can u pls provide the reference links to understand more about this.
I'm not sure if this is documented anywhere (if it is, I've never found it), but the explanation is that SQL Server uses is a one-pass parser and comiler. That implies that it reads the code once, top to bottom and left to right. Variables have to be declared before they are referenced, and exactly once. Similar for table creation and referencing.
One effect of this one-pass parser is that control-flow is not evaluated. So a variable declaration in a loop is not an error, and creating a table in a loop will not cause a compile-time error (the error will be at run-time, during the second execution of the loop). It is also possible to have declarations in code paths that will never execute. For instance, this code compiles and executes just fine:
IF 1 = 2
BEGIN;
DECLARE @a int;
END;
SET @a = 1;
PRINT @a;
And similarly, this code will execute without errors, but fails at run-time.
IF 1 = 2
BEGIN;
CREATE TABLE #xyz (a int)
END;
INSERT INTO #xyz(a) VALUES(1);
SELECT * FROM #xyz;
This rarely ever affects "normal" code - except in cases such as this question, where people try to use SELECT INTO in two different branches of an IF statement.
Iulian -207023 (11/18/2010)
Great point. Amazing, the compiler see two creations of the #tmpID table and stops the procedure from compiling.Do you have at hand some best practices for using DML statements? I am thinking on something like:
No, I would not recommend resorting to dynamic SQL. As the explanation of the question suggests, the best course of action is to create the table seperately, then fill it in the IF. So the code in this question should change to either
CREATE PROCEDURE QOTD (@source INT)
AS
BEGIN;
CREATE TABLE #tmpID (ID int);
IF @source = 1
INSERT INTO #tmpID (ID)
SELECT ID FROM tbl1;
ELSE
INSERT INTO #tmpID (ID)
SELECT ID FROM tbl2;
SELECT ID FROM #tmpID;
END;
or
CREATE PROCEDURE QOTD (@source INT)
AS
BEGIN;
SELECT ID INTO #tmpID FROM tbl1 WHERE 1 = 2;
IF @source = 1
INSERT INTO #tmpID (ID)
SELECT ID FROM tbl1;
ELSE
INSERT INTO #tmpID (ID)
SELECT ID FROM tbl2;
SELECT ID FROM #tmpID;
END;