In a procedure I wrote, I wanted to allow the flexibility to support various types for a flat-file. This required an IF...ELSE
block. Inside the block, the data would be imported, scrubbed, and loaded into a temporary table. On a high level, this is what I was attempting to do:
DECLARE @filetype varchar(10); BEGIN SELECT * INTO #aTemp FROM OPENROWSET( ... ); IF @filetype = 'xlsx' BEGIN SELECT ... INTO #bTemp FROM #aTemp; END ELSE BEGIN SELECT ... INTO #bTemp FROM #aTemp; END ... --(transform/load #bTemp) END |
This however, raised the error:
Msg 2714, Level 16, State 1, Line 10 There is already an object named '#bTemp' in the database.
I also attempted adding a DROP TABLE
inside each block. After some research, the documentation states that inside a stored procedure, the temporary tables must have different names:
If more than one temporary table is created inside a single stored procedure or batch, they must have different names.
The solution was to create the temporary table outside the IF...ELSE
block and then INSERT
the data inside the blocks:
DECLARE @filetype varchar(10); BEGIN IF OBJECT_ID('[tempdb]..#bTemp') IS NOT NULL BEGIN DROP TABLE #bTemp; END CREATE TABLE #bTemp ( [c] int); IF @filetype = 'xlsx' BEGIN INSERT INTO #bTemp SELECT 1 AS [c]; END ELSE BEGIN INSERT INTO #bTemp SELECT 1 AS [c]; END DROP TABLE #bTemp; END |
Also, when defining temporary tables inside a stored procedure, it’s advised to explicitly specify NULL
or NOT NULL
for each column:
It is recommended that you explicitly specify
NULL
orNOT NULL
for each column in anyCREATE TABLE
orALTER TABLE
statement in a stored procedure, such as when creating a temporary table.
Update
As Sebastian discusses in his blog post, it’s better practice to allow the procedure to naturally drop the temporary tables created inside. This would leave:
DECLARE @filetype varchar(10); BEGIN CREATE TABLE #bTemp ( [c] int); IF @filetype = 'xlsx' BEGIN INSERT INTO #bTemp SELECT 1 AS [c]; END ELSE BEGIN INSERT INTO #bTemp SELECT 1 AS [c]; END END |