PRB: Creating Temp Table Inside IF and ELSE Conditions Gives Error 2714 (295305)

SYMPTOMS

When CREATE TABLE statements using the same table name are issued inside both the IF block and the ELSE block of an IF..ELSE statement, error 2714 may be received.

For instance, the following code

CREATE PROCEDURE test as:
DECLARE @var INT
SET @var=1

IF @var = 1
BEGIN 
CREATE TABLE #temp (c1 INT)
END
ELSE 
BEGIN
CREATE TABLE #temp (c1 VARCHAR(2))
END

results in the following error:

Server: Msg 2714, Level 16, State 1, Procedure test, Line 9
There is already an object named '#temp' in the database.

WORKAROUND

To work around this problem, issue the CREATE TABLE statement outside the IF..ELSE statement. To change the table structure inside the IF..ELSE statement, use the ALTER TABLE statement. For example:

CREATE PROCEDURE test as:
DECLARE @var INT
SET @var=1
CREATE TABLE #temp (c1 INT)
IF @var = 1
BEGIN
ALTER TABLE #temp ALTER COLUMN c1 INT
END
ELSE 
BEGIN
ALTER TABLE #temp  ALTER COLUMN c1  VARCHAR(2)
END

It is not possible to directly reference these new column names from within the IF clause. To do this, use a nested stored procedure and reference the new column names in that procedure. For example, if the code is as follows:

CREATE PROCEDURE test AS
DECLARE @var INT
SET @var=2
CREATE TABLE #temp (c1 INT)
IF @var = 1
BEGIN
ALTER TABLE #temp ALTER COLUMN c1 INT
END
ELSE
BEGIN
ALTER TABLE #temp ADD c2 VARCHAR(3)
INSERT INTO #temp(c2) VALUES ('abc')
SELECT * FROM #temp WHERE c2='abc'
END

when the procedure created above is executed, the following error is returned:

Server: Msg 207, Level 16, State 1, Procedure test, Line 14
Invalid column name 'c2'.
Server: Msg 207, Level 16, State 1, Procedure test, Line 15
Invalid column name 'c2'.

This is because the INSERT statement cannot see the altered column. To see the altered column, the INSERT must be executed from a nested stored procedure:

CREATE PROCEDURE nestedproc AS
INSERT INTO #temp(c2) VALUES ('abc')
SELECT * FROM #temp WHERE c2='abc'

ALTER PROCEDURE test AS
DECLARE @var INT
SET @var=2
CREATE TABLE #temp (c1 INT)
IF @var = 1
BEGIN
ALTER TABLE #temp ALTER COLUMN c1 INT
END
ELSE
BEGIN
ALTER TABLE #temp ADD c2 VARCHAR(3)
EXEC nestedproc
END