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
– Thom A ♦, Commented Jan 6, 2022 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
– Thom A ♦, Commented Jan 6, 2022 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
– Thom A ♦, Commented Jan 6, 2022 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
– N. Kaufman, Commented Jan 6, 2022 at 16:35

Thom A · Accepted Answer · 2022-01-06 16:40:48Z

3

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;

answered Jan 6, 2022 at 16:40

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create temp table from dynamic SQL with parameters

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related