HOW TO - Resolve 'An INSERT EXEC statement cannot be nested' Error/Problem

  • YSLGuru

    SSC-Insane

    Points: 21301

    Assuming I correctly understand this, the error msg is saying I can't use a Stored procedure to call another Stored Procedure. My question is

    1) How to work-a-round this

    2) Anyone know why this limitation even exists?

    I've always used the "Create Temp Table - Store SP Results In Temp table - Return Temp Table " approach to get greater control (i.e sorting, column order, column selection, results filtering) over the results from a Stored procedure and its always worked. Now I run into this limitation because I am for the first time trying to make a SP call from within an SP call.

    My inner SP calls the xp_CmdShell Extended SP. This SP I created works great and I would like the ability to call it and do so many times and take the results from it and place them in a temp table and this is where I ran into this error.

    I could take the code in my SP that uses the xp_CmdShell Ext SP and put it in this new outer SP but this doesn't make sense from a logical perspective. It's like reinventing the wheel. While SSQL is set based and traditional programming langauges are not it just seems illogical you can't call an SP from another SP.

    So how do you get around this limitation other then recreating the SP so that your outer SP contains all the code your Inner SP did?

    Thanks

    Kindest Regards,

    Just say No to Facebook!
  • Orlando Colamatteo

    SSC Guru

    Points: 182268

    Assuming I correctly understand this, the error msg is saying I can't use a Stored procedure to call another Stored Procedure.

    No, that is not what it is telling you.

    It is telling you is that you cannot have a proc that does an INSERT-EXEC operation call another proc that also does an INSERT-EXEC operation.

    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

  • Orlando Colamatteo

    SSC Guru

    Points: 182268

    Erland has been around the SQL block a few times and he makes some pretty good points as to why not to use INSERT-EXEC as a general development technique:

    http://www.sommarskog.se/share_data.html#INSERTEXEC

    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

  • YSLGuru

    SSC-Insane

    Points: 21301

    I'm all for BEST PRACTICES so if there is a better way to do this then using the INSERT-EXEC method I'm open to trying it.

    I use this now to call xp_CmdShell to run a Powershell command to obtain the the last time a system, not the SQL Server Engine instance, rebooted. If there is a way in T-SQL to do this I'd love to hear about. I spent a decent bit of time trying to avoid using a powershell script and stay within native T-SQL but all 'last start' solutions returned when TempDB was created or when the server engine restarted .

    Ideas?

    Thanks

    Kindest Regards,

    Just say No to Facebook!
  • Orlando Colamatteo

    SSC Guru

    Points: 182268

    I would setup a SQL Agent job that was scheduled to "Start automatically when SQL Agent starts" to call your PowerShell script, retrieve the "last reboot time" from the OS and persist that value in a configuration table somewhere...then you do not need xp_cmdshell or INSERT-EXEC, you can just grab the value when you need it using standard T-SQL.

    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

  • Orlando Colamatteo

    SSC Guru

    Points: 182268

    To clarify...I am implying that you would enhance your PowerShell script to update the configuration table in the database with the "last reboot time".

    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

  • jamrose

    SSC Enthusiast

    Points: 127

    Ok, so here's something weird to throw into the mix.

    I found this thread because of the error when I was trying to run the results of this query in SQL 2005.

    INSERT INTO#temp_subscriberDetails

    EXEC sp_helpsubscription (select db_name())

    I get the error message.

    An INSERT EXEC statement cannot be nested.

    But the query DOES insert the records into my table.

    I thought I would try running this instead to get round the error message:

    BEGIN TRY

    INSERT INTO#temp_subscriberDetails

    EXEC sp_helpsubscription (select db_name())

    END TRY

    BEGIN CATCH

    END CATCH

    When I run this no error message is displayed (which I understand) but no records are inserted into the table, which I don't understand.

  • Orlando Colamatteo

    SSC Guru

    Points: 182268

    If you look at the code for sys.sp_helpsubscription you'll notice it calls two procs, sys.sp_MSrepl_getpublisherinfo and sys.sp_MSrepl_helpsubscription. Chances are the proc sys.sp_MSrepl_getpublisherinfo does not make use of INSERT EXEC and is successfully returning a resultset without throwing an error, which you are capturing in your temp table. The error is likely generated out of the second proc call sys.sp_MSrepl_helpsubscription so while you get data in your temp table, the call to sys.sp_helpsubscription results in an error.

    As to why it does not work this way when used inside a try/catch, that's interesting. I can confirm all behavior you're describing on SQL 2005 Standard build 9.00.4060.00.

    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

  • YSLGuru

    SSC-Insane

    Points: 21301

    opc.three (3/15/2011)


    To clarify...I am implying that you would enhance your PowerShell script to update the configuration table in the database with the "last reboot time".

    I understand. Thanks

    Kindest Regards,

    Just say No to Facebook!
  • ajkait

    SSC Enthusiast

    Points: 117

    For what it is worth, we have gotten around this by having the called SP return a text string containing the offending SQL code to the calling SP, then executing the code WITHIN the calling SP, rather than calling on the second SP to do the EXEC.

    Further, so that the subordinate SP can be called directly for its data output, or called by another SP for further use of the data, we configure it with an output variable as a switch. If we call it with the output variable NULL, it EXECUTES the internal SQL and passes back the data (e.g., to an ASP.NET request). If we need to use it in another SP, we we pass an output variable to it, and get back the SQL text that we then execute inside the other SP.

    Here is a simple example which uses a table tblDevices which has fields Device_Id (int) and FirstName (varchar(30)) amongst other things. There is a record with Device_Id = 10275.

    Subordinate SP (@r is the "switch")

    CREATE PROCEDURE [dbo].[sp_TestNested1] (@Device_ID Int, @r VARCHAR(MAX) out)

    AS

    BEGIN

    DECLARE @sSql VARCHAR(MAX)

    SELECT @sSql = 'DECLARE @Table TABLE(FirstName VARCHAR(30))

    BEGIN

    INSERT INTO @Table(FirstName) (SELECT FirstName FROM tblDevices WHERE Device_Id = ' + CAST(@Device_Id AS VARCHAR(10)) + ') END

    SELECT * FROM @Table'

    END

    BEGIN

    IF @r IS NULL

    BEGIN

    EXEC(@sSql)

    END

    ELSE

    BEGIN

    SELECT @r = @sSql

    END

    END

    Calling SP

    CREATE PROCEDURE [dbo].[sp_TestNested2] (@Device_ID Int)

    AS

    BEGIN

    Declare @r VARCHAR(Max)

    EXEC dbo.sp_TestNested1 10275, @r OUTPUT

    EXEC(@r)

    END

    --------------------------------

    So, if we

    EXEC dbo.sp_TestNested1 10275,null, it executes the SQL and we get back the results

    If we EXEC dbo.sp_TestNested2 10275, it passes the @r switch, gets back the SQL in @r, and executes the dbo.sp_TestNested1 code internally.

    At the end of the day, we have a single object in dbo.sp_TestNested1 that can either be executed itself, or used by any number of other SPs that need to do something with its results without duplicating the dbo.sp_TestNested1 code.

Viewing 10 posts - 1 through 10 (of 10 total)

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