0

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';
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
sphs885
  • 1

1 Answers1

0

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;
Stu
  • 30,392
  • 6
  • 14
  • 33
  • I can't seem to get this to work. SQL is telling me that one of the variables must be defined - "Must declare scalar variable @stamp' – sphs885 Sep 07 '22 at 19:44
  • @sphs885 I was assuming `@stamp` was already defined since it's a parameter of your procedure? for testing *outside* of the procedure you will need to declare it `declare @stamp int = 101` – Stu Sep 07 '22 at 20:33