Dynamic SQL? Inserting Sproc data into table variable

Question

Post reply

Dynamic SQL? Inserting Sproc data into table variable

tacy.highland

Hall of Fame

Points: 3319

October 1, 2021 at 11:37 pm

#3935729

Hello,

I have a bunch of stored procs that I'd like to execute all at once, insert their results into a table variable, and then insert those results into a physical table. I can get the code to run fine to actually execute all the sprocs one after the other using a cursor (there aren't that many, less than 20) but now that I want to actually insert the data for each one into the table variable, it doesn't like it. See below (for brevity's sake I left out the detail on the table definition)

 DECLARE @SQL NVARCHAR(MAX)          
   DECLARE @ProjectID INT
   DECLARE @SLDate INT = 20210930  
   DECLARE @ELDate INT = 20210930 


DECLARE  @Results AS TABLE
(
[columns]...
)



     DECLARE db_cursor CURSOR FOR

       SELECT [ProjectID]
       FROM [Project_Table]

     OPEN db_cursor  

  FETCH NEXT FROM db_cursor INTO @ProjectID  

       WHILE @@FETCH_STATUS = 0  

            BEGIN               
SET @SQL = '[' + CAST(@ProjectID AS VARCHAR(7)) + '].' + 'StoredProcName ' + CAST(@SLDate AS VARCHAR) + ', ' + CAST(@ELDate AS VARCHAR)


INSERT INTO @Results
EXEC sp_executesql @SQL 
   
            FETCH NEXT FROM db_cursor INTO @ProjectID  

            END  

       CLOSE db_cursor  

       DEALLOCATE db_cursor

  
SELECT *
FROM @RESULTS

Can anyone see what I missed here? I don't do this type of thing a lot so not sure what the issue may be.

Thanks!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101095 More actions · Answer 1

I have to admit, I really don't know how to debug "it doesn't like it." I'm not even 100% sure what that specifically means.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 2

Jonathan AC Roberts

SSCoach

Points: 18354

October 2, 2021 at 9:31 am

#3935807

Did you get an error message?

Steve Collins SSCrazy Points: 2157 More actions · Answer 3

I just ran into this same issue. The short answer is to create a #temp table instead and it works (because the temp table exists within the same execution context as the procedure). Passing declared variables to dynamic sql involves an additional declaration and it wasn't clear how to do it with a declared table.

This syntax works with a declared variable

declare @c        int=1;
declare @sql      nvarchar(max) = 'select @cvar;'
exec sp_executesql @sql, N'@cvar int', @cvar=@c;

Similar approach using declared table doesn't work

declare @t        table(col   int);
declare @sql      nvarchar(max) = 'select * from @tbl';
exec sp_executesql @sql, N'@tbl   table(col   int)', @tbl=@t;

Msg 156, Level 15, State 1, Line 5384
Incorrect syntax near the keyword 'table'.
Msg 1087, Level 15, State 2, Line 5384
Must declare the table variable "@t".

This works

create table #t(col   int);
insert #t values (1);
declare @sql      nvarchar(max) = 'select * from #t';
exec sp_executesql @sql;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

tacy.highland Hall of Fame Points: 3319 More actions · Answer 4

Apologies for not including more detail on the issue. The error is"An INSERT EXEC statement cannot be nested."

I tried changing the table variable to a temp table but still get the same error.

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 5

tacy.highland wrote:

Apologies for not including more detail on the issue. The error is"An INSERT EXEC statement cannot be nested."
I tried changing the table variable to a temp table but still get the same error.

That means that you have INSERT EXEC's in your stored procedures and what you're trying to do isn't going to work.

You might be able to do a work around with OPENROWSET.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

tacy.highland Hall of Fame Points: 3319 More actions · Answer 6

ugh. You're right. I completely forgot about the OPENQUERY inside the stored procs I'm trying to execute.

Ok, well, perhaps this won't work for what I need to do....