Post reply

Problem inserting return from stored procedure into a table

  • December 19, 2014 at 8:33 am

    #293593

    Hi All

    Ending up with the error "Msg 50000, Level 11, State 11, Procedure Usp_CopyProcedure, Line 724"

    USE AdventureWorks;

    GO

    DECLARE @MyTableVar table(

    FirstName varchar(30),

    LastName varchar(30)

    );

    INSERT @MyTableVar

    EXEC dbo.Usp_CopyProcedure

    Msg 50000, Level 11, State 11, Procedure Usp_CopyProcedure, Line 724

    How do i get rid of this issue, appriciate your help

    Regards

    John

  • December 19, 2014 at 12:40 pm

    #1765788

    What's at line 724 of Usp_CopyProcedure?

    Gerald Britton, Pluralsight courses

  • December 19, 2014 at 12:56 pm

    #1765792

    i can tell you it's an explicit error that someone coded to capture in your procedure, where someone is using the RAISERROR('some string',16,1).

    as noted, read the proc and jump to line 724.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 19, 2014 at 12:56 pm

    #1765793

    i can tell you it's an explicit error that someone coded to capture in your procedure, where someone is using the RAISERROR('some string',11,11).

    as noted, read the proc and jump to line 724.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 22, 2014 at 2:27 am

    #1765925

    Hi

    Thanks for your reply, yes RAISERROR statement is there at that particular statement but how can i overcome this issue

    Thanks

    John

  • December 22, 2014 at 2:37 am

    #1765927

    Look at what conditions would cause the RAISERROR line to be executed.

    Open up the proc, go to line 724 and work out, from the code, what it does and what would make the RAISERROR run

    It's a custom error, it's something that the developer put into that procedure, it's not something we can help you with.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 22, 2014 at 6:23 am

    #1765958

    Yes, actually the inner procedure is inserting the procedure1 output into a temporary table 1 and again the procedure2 is trying to insert the resultset into temporary table 2, so we are getting an error saying that

    "An INSERT EXEC statement cannot be nested."

  • December 22, 2014 at 6:26 am

    #1765960

    Well, there you have it. INSERT .. EXEC cannot be nested.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 22, 2014 at 6:59 am

    #1765972

    I agree with you, it used to work, recently we have added a column to the temp table in prod env, then it is giving the exception, will work on the oter alternatives to avoid this, appriciate if can tell us something

    Thanks

    John

  • December 22, 2014 at 7:13 am

    #1765977

    If it worked previously, then you weren't trying to nest INSERT ... EXEC as they cannot be nested under any circumstances. You're going to have to figure out an alternative way of doing things that doesn't require nesting them

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 23, 2014 at 12:55 am

    #1766122

    convert your SP into Table-Valued function to use nested insert

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

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