How to drop temporary table in Stored Procedure

Question

I am using SQL Server 2016 Service Pack 2 and use the following code. It executes correctly in SQL query statement:

SELECT 'a' AS one INTO #deleted;
DROP TABLE IF EXISTS #deleted;
GO

SELECT 'b' AS two INTO #deleted;
DROP TABLE IF EXISTS #deleted;
GO

But when I picking up GO I am faced with this error:

There is already an object named '#deleted' in the database.

If I want to create a SP with this syntax I can't use GO.

Answer 1

Correct. You will have to give the 2 temp tables different names if you want to use them in the same batch using SELECT INTO statements with a DIE statement in between.

Alternatively, you can create the temp table first, with all the required columns for both SELECT statements, then adjust the SELECTs (or whatever statements you're running with the temp table data) accordingly.

E.g.

BEGIN
    DROP TABLE IF EXISTS #deleted ;
    CREATE TABLE #deleted ( [one] VARCHAR(5) NULL, [two] VARCHAR(5) NULL ) ;

    INSERT  INTO #deleted ( [one] )
    SELECT 'a' AS [one] ;

    INSERT  INTO #deleted ( [two] )
    SELECT  'b' AS [two] ;

    SELECT [one] FROM #deleted WHERE [two] IS NULL ;
    SELECT [two] FROM #deleted WHERE [one] IS NULL ;

    DROP TABLE IF EXISTS #deleted ;
END
GO

Update 1
As already mentioned, you could just give the temp tables different names.

E.g.

BEGIN
    SELECT  'a' AS [one]
    INTO    #deleted1 ;
    SELECT  * FROM #deleted1 ;  -- or whatever statements you need to execute
    DROP TABLE IF EXISTS #deleted1 ;

    SELECT  'b' AS [two]
    INTO    #deleted2 ;
    SELECT  * FROM #deleted2 ;  -- or whatever statements you need to execute
    DROP TABLE IF EXISTS #deleted2 ;
END
GO

Answer 2

Another option: you can truncate the table and re-use it (this should be very efficient, compared to using a delete query). The only difference in practice is that the column name would be the same in both uses of the table:

DROP TABLE IF EXISTS #deleted;
SELECT 'a' AS [Value] INTO #deleted;
TRUNCATE TABLE #deleted;
INSERT INTO #deleted SELECT 'b';
GO