Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2004
    Posts
    285
    Provided Answers: 1

    Unanswered: Multiple recrodsets retruned by 1 Stored Procedure

    Good day,

    I have a Stored Procedure that returns 2 recordsets. It is an encrypted SP from a 3rd party developer so I am not able to modify it or view the contents there of. I need to work with one of the 2 recordsets returned by the SP, the 1st one to be precise.

    I need to use the 1st recordset in another SP but have no idea how to do it as I need to manipulate the data, join the recordset to a table and thought the best way to do it is to load the recordset into a temp table and then using that temp table do a join but I can't figure out how to load the 1st recrodset into the temp table, gives me an error:
    An INSERT EXEC statement cannot be nested

    Please can anyone help?

  2. #2
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Have you tried declaring a table variable--something like this:

    Code:
    declare @ProjectList
    table	(
    	ProjectNumber varchar(10) NOT NULL,
    	PRIMARY KEY 
    		(
    		ProjectNumber 
    		)
    	)
    . . . and then inserting from your original stored procedure--something like this:

    Code:
    	
    insert
    into	@ProjectList
    exec	usp_SelectProjects
    You can then use the @ProjectList table variable just like any other table.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  3. #3
    Join Date
    Nov 2004
    Posts
    285
    Provided Answers: 1
    I have tried using a temp table but not @ProjectList, does that make a difference? Also the 2 recordsets returned are different in data and makeup in that the one has about 12 fields and the other 4 fields.

  4. #4
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    No, it should not make a difference.

    Make your temporary table (or table variable) 12 columns in size, make the columns "universal" (like a varchar) so they can accept most any type of data.

    Then use the INSERT INTO to pull both recordsets into this temporary table, and then delete any records where the last eight fields are all NULL (or there are values in the last eight fields)

    Depending upon whether you delete is based upon the last eight fields being all NULL, or the last eight fields having values, you will be left with the single recordset.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  5. #5
    Join Date
    Nov 2004
    Posts
    285
    Provided Answers: 1
    I still get the same error:
    An INSERT EXEC statement cannot be nested.

    I get this error repeated 10 times and then an error:
    Insert Error: Column name or number of supplied values does not match table definition.

    I have 13 columns defined in my temp table and 13 columns in my first recordset and 4 in my second recordset.

  6. #6
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Then you haven't told us the whole story. There is something in your code that is producing this error that you haven't told us about. Please post your code.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  7. #7
    Join Date
    Nov 2004
    Posts
    285
    Provided Answers: 1
    Stored procedure returns 2 recordset.
    Recordset A has 13 columns of differing datatypes
    Recrodset B has 4 colums of differing datatypes

    This is my code:
    DECLARE @TempTable Table (
    Field1 nvarchar(75),
    Field2 nvarchar(75),
    Field3 nvarchar(75),
    Field4 nvarchar(75),
    Field5 nvarchar(75),
    Field6 nvarchar(75),
    Field7 nvarchar(75),
    Field8 nvarchar(75),
    Field9 nvarchar(75),
    Field10 nvarchar(75),
    Field11 nvarchar(75),
    Field12 nvarchar(75),
    Field13 nvarchar(75))
    Insert into @TempTable EXEC spLocal_ShippingOrders '7429'

    And then I get the following errors:
    Msg 8164, Level 16, State 1, Procedure spLocal_ShippingOrders, Line 485
    An INSERT EXEC statement cannot be nested.
    Msg 8164, Level 16, State 1, Procedure spLocal_ShippingOrders, Line 485
    An INSERT EXEC statement cannot be nested.
    Msg 8164, Level 16, State 1, Procedure spLocal_ShippingOrders, Line 485
    An INSERT EXEC statement cannot be nested.
    Msg 8164, Level 16, State 1, Procedure spLocal_ShippingOrders, Line 485
    An INSERT EXEC statement cannot be nested.
    Msg 8164, Level 16, State 1, Procedure spLocal_ShippingOrders, Line 485
    An INSERT EXEC statement cannot be nested.
    Msg 8164, Level 16, State 1, Procedure spLocal_ShippingOrders, Line 485
    An INSERT EXEC statement cannot be nested.
    Msg 8164, Level 16, State 1, Procedure spLocal_ShippingOrders, Line 485
    An INSERT EXEC statement cannot be nested.
    Msg 8164, Level 16, State 1, Procedure spLocal_ShippingOrders, Line 485
    An INSERT EXEC statement cannot be nested.
    Msg 8164, Level 16, State 1, Procedure spLocal_ShippingOrders, Line 485
    An INSERT EXEC statement cannot be nested.
    Msg 8164, Level 16, State 1, Procedure spLocal_ShippingOrders, Line 485
    An INSERT EXEC statement cannot be nested.
    Msg 213, Level 16, State 7, Procedure spLocal_ShippingOrders, Line 765
    Insert Error: Column name or number of supplied values does not match table definition.

    Column size and datatype of nvarchar(75) is more than big enough for what is expected to go into the field.

  8. #8
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Although I am not getting the same error as you, I have confirmed that it might not be possible to accomplish what you and I have set out to do here.

    Here is what I did for a test case:

    Code:
    create procedure usp_Test
    
    as
    
    select 'Value1','Value2'
    select 'Value1','Value2','Value3','Value4','Value5'
    GO
    
    declare	@TestTable
    table	(
    		Field1 varchar(10)
    		,Field2 varchar(10)
    		,Field3 varchar(10)
    		,Field4 varchar(10)
    		,Field5 varchar(10)
    		)
    		
    insert
    into	@TestTable
    		(
    		Field1
    		,Field2
    		,Field3
    		,Field4
    		,Field5
    		)
    exec	usp_Test
    
    select * from @TestTable
    And here is the resulting error that I got:

    Code:
    Msg 213, Level 16, State 7, Procedure usp_Test, Line 5
    Column name or number of supplied values does not match table definition.
    I tired with and without the field list on the INSERT INTO.
    I tried with a temporary table rather than a table variable.
    I tried various other minor machinations.
    None successful.

    The basic problem is that the stored procedure returns two recordsets--a bad idea from the start.

    Sorry couldn't help.

    Maybe someone else out there has a solution.

    A crazy thought--perhaps you could execute this stored procedure, directing its output to a text file. Then you could re-import it as a single recordset. This idea is just off the top of my head and it is a real kludge, however . . .
    Ken

    Maverick Software Design

    (847) 864-3600 x2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •