Create temp table from dynamic SQL with parameters

Question

Here is my issue. I need to create a temp table after executing dynamic SQL and passing params as follows

CREATE PROCEDURE SP1
    @param1 varchar(50),
    @param2 varchar(50)
AS
BEGIN
    DECLARE @PDef varchar(300)
    DECLARE @sql  varchar(300)
    DECLARE @localparam1 varchar(300)
    DECLARE @localparam2 varchar(300)

    SET @localparam1 = ....
    SET @localparam2 = ....
 
    SET @PDef = '@param1 varchar(50), @localparam1 varchar(300)'

    SET @sql = 'SELECT * FROM TABL1 WHERE COL1 = @param1, COL2 in (@localparam1)'
 
    EXEC sp_Executesql @sql, @PDef, 
                            @param1 = @param1, @localparam1 = @localparam1

The above works. How do I get the results into a temp table?

I tried

CREATE TABLE #T1 (col1 varchar(50), col2 varchar(50) )

INSERT INTO #T1 
    EXECUTE @sql    -- didn't work    

INSERT INTO #T1 
    EXECUTE (@sql, @PDef, @param1 = @param1, @localparam1 = @localparam1)   -- didn't work either

EDIT: Had Looked at the following samples while using EXECUTE Dynamic SQL results into temp table in SQL Stored procedure and hence used EXECUTE The accepted answer was: INSERT into #T1 execute ('execute ' + @SQLString ) omit the 'execute' if the sql string is something other than a procedure

Now see the comments to that accepted answer that question that accepted answer :-)

You removed `sp_Executesql` from your `EXEC` statement. Voting to close as typographical error. — Thom A, Jan 06 '22 at 16:28
Though, why are you using dynamic SQL at all here? There's nothing dynamic about your statement. Also `COL2 in (@localparam1)` isn't likely to do with you think it will; it is equivalent to `COL2 = @localparam1`. — Thom A, Jan 06 '22 at 16:30
`insert into #T1 execute sys.sp_Executesql @sql` didn't work? What does "didn't work" mean? Did you get an error? Unexpected results? Something else? — Thom A, Jan 06 '22 at 16:30
get error when I use - insert into #T1 execute sys.sp_Executesql @SQL Its looking for params- "Must declare the scalar variable .." — N. Kaufman, Jan 06 '22 at 16:35
And that error is what...? You *did* pass the parameters like you did in your procedure, right..? — Thom A, Jan 06 '22 at 16:36
this worked insert into #T1execute sys.sp_Executesql @sql,@PDef,... — N. Kaufman, Jan 06 '22 at 16:39

score 3 · Answer 1 · answered Jan 06 '22 at 16:40

You've removed sp_executesql from your query for some reason. You can't called sys.sp_executesql if you don't tell SQL Server to:

INSERT INTO #T1
EXECUTE sys.sp_executesql @sql;

INSERT INTO #T1
EXECUTE sys.sp_executesql @sql,
                          @PDef,
                          @param1 = @param1,
                          @localparam1 = @localparam1;

Create temp table from dynamic SQL with parameters

1 Answers1