Problem in Creating stored procedure

Question

I am creating a stored procedure but it throws an error. Can anybody tell me if there is something i am doing wrong.

CREATE TABLE tbl1 (ID int)
INSERT INTO tbl1 SELECT 1
CREATE TABLE tbl2 (ID int)
INSERT INTO tbl2 SELECT 2

CREATE PROCEDURE QOTD (@source INT)
AS 
BEGIN
 IF @source = 1 
 SELECT ID INTO #tmpID FROM tbl1 
 ELSE
 SELECT ID INTO #tmpID FROM tbl2 

 SELECT ID FROM #tmpID
END

ERROR:There is already an object named '#tmpID' in the database.

@Mitch Wheat, how do you explain that error at compile time though? I'm pretty sure it's because it is a syntax error to do SELECT ID INTO #tmpID FROM tbl1 and not because the object already exists. — Klaus Byskov Pedersen
– Klaus Byskov Pedersen, Commented Nov 18, 2010 at 12:11

Damien_The_Unbeliever · Accepted Answer · 2010-11-18 12:07:13Z

7

Sometimes the T-SQL compiler gets confused, and doesn't realise that only one side of an IF would be followed.

CREATE PROCEDURE QOTD (@source INT)
AS 
BEGIN
 CREATE TABLE #tmpID(ID int)
 IF @source = 1 
 INSERT INTO #tmpID SELECT ID FROM tbl1 
 ELSE
 INSERT INTO #tmpID SELECT ID  FROM tbl2 

 SELECT ID FROM #tmpID
END

answered Nov 18, 2010 at 12:07

Damien_The_Unbeliever

241k28 gold badges358 silver badges470 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Lieven Keersmaekers Over a year ago

Not a day goes by without learning something new. +10 if I could but I've used up my votes for today...

Damien_The_Unbeliever Over a year ago

@Lieven - I frequently find myself trying to think like the T-SQL compiler. The trick is to say to oneself "what's the stupidest thing I could do here?"

Klaus Byskov Pedersen · Accepted Answer · 2010-11-18 12:07:10Z

1

You don't need to use a temporary table. This should be enough:

CREATE PROCEDURE QOTD (@source INT) 
AS  
BEGIN 
 IF @source = 1
    SELECT ID FROM tbl1  
 ELSE 
    SELECT ID  FROM tbl2  
END

answered Nov 18, 2010 at 12:07

Klaus Byskov Pedersen

122k31 gold badges192 silver badges223 bronze badges

Comments

pavanred · Accepted Answer · 2010-11-18 12:06:50Z

-2

There already exists a temporary table #tmpID in your database. Try using this before your procedure.

Drop table #tmpID

answered Nov 18, 2010 at 12:06

pavanred

14k16 gold badges56 silver badges60 bronze badges

Collectives™ on Stack Overflow

Problem in Creating stored procedure

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related