0

using temporary tables inside if block with a procedure

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 or NOT NULL for each column in any CREATE TABLE or ALTER 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

Norbert Krupa

Technical Consultant

Leave a Reply

Your email address will not be published.