June 29, 2007 at 2:14 am
Hi,
I have this code construct in a stored procedure
IF <codition1>
BEGIN
SELECT pKey, Column1 INTO #Tmp1 FROM Items
WHERE <condition2>
END
ELSE
BEGIN
SELECT pKey, Column1 INTO #Tmp1 FROM Items
WHERE <condition3>
END
This fails with Msg2714: There is already an object named '#Tmp1' in the database.
I guess this fails because an execution plan is created when compiling a stored procedure, and because it cannot determine if <condition1> is true or not - it generates plans for both SELECT statements, failing at the second.
I can work around this by creating the temporary table before the IF statement and doing INSERT INTO #Tmp1 instead,
HOWEVER: If I use real tables, instead of temporary tables
i.e. Tmp1 instead of #Tmp1 - there is no error!
I would be interested to hear people's comments.
June 29, 2007 at 2:49 am
June 29, 2007 at 7:12 am
I can work around this by creating the temporary table before the IF statement and doing INSERT INTO #Tmp1 instead
It is not a workaround, it would be the preferred way.
SELECT pKey, Column1 INTO #Tmp1 FROM Items WHERE 0=1
IF <condition1>
BEGIN
INSERT INTO #Tmp1
SELECT pKey, Column1 FROM Items
WHERE <condition2>
END
ELSE
BEGIN
INSERT INTO #Tmp1
SELECT pKey, Column1 FROM Items
WHERE <condition3>
END
But bledu is right, a single SELECT statement is better. But his WHERE statement is slight wrong. You will want:
WHERE (<condition1> AND <condition2> ) OR
(NOT <condition1> AND <condition3> )
Hope this helps
Mark
June 29, 2007 at 7:39 am
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply