Dynamic SQL? Inserting Sproc data into table variable

  • 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!

  • 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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Did you get an error message?

  • 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

  • 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.

  • 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)
    Intro to Tally Tables and Functions

  • 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....

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

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