Microsoft KB Archive/295305

= PRB: Creating Temp Table Inside IF and ELSE Conditions Gives Error 2714 =

Article ID: 295305

Article Last Modified on 10/16/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q295305



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

Keywords: kbprb KB295305

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.