sql-server - 创建存储过程时出现问题

Question

我正在创建一个存储过程，但它抛出一个错误。谁能告诉我我是否做错了什么。

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.

Answer 1

有时，T-SQL 编译器会感到困惑，并且没有意识到只会遵循 IF 的一侧。

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