Dynamically assign global temp table name in stored procedure - SQL Server Management Studio

Question

I would like to create multiple global temp tables using the same stored procedure. When I run the below I get table_@STAMP. How can I do this?

CREATE PROCEDURE ##CRTTBL 
     (@STAMP as INT, 
      @DATE as DATETIME)
AS
    SELECT *
    INTO ##table_@STAMP 
    FROM mytable 
    WHERE STARTDATE = @DATE AND STAMP = @STAMP; 


EXECUTE ##CRTTBL @STAMP = 101, @DATE = '01/01/2022';
EXECUTE ##CRTTBL @STAMP = 102, @DATE = '01/01/2022';

Answer 1

You would need dynamicSQL for this in order to define the table name, and then execute with sp_executesql and pass in the query parameters.

Untested, but something like the following:

declare @sql nvarchar(max), @params nvarchar(100) = '@date datetime, @stamp int';

set @sql = Concat('
    SELECT * INTO ##table_', @stamp, ' 
    FROM mytable 
    WHERE STARTDATE = @DATE AND STAMP = @STAMP'
);

exec sp_executesql @sql, @params, @date = @date, @stamp = @stamp;