CODEWITHSUNDEEP
sqlsql-serversql-server-2008stored-procedures
JD Stuart
JD Stuart

Reputation: 557

Use nested stored procedure results in calling stored procedure Sql Server 2008

Is it possible to use the results of one stored procedure in another stored procedure?

I.e.

CREATE PROCEDURE [dbo].[Proc1]
        @ID INT,
        @mfgID INT,
        @DealerID INT

AS
BEGIN

    DECLARE @Proc1Result UserDefinedTableVariable

    EXEC @Proc1Result = Proc2
        @SomeID = @ID,
        @SomeID2 = @mfgID,
        @SomeID3 = @DealerID

    -- Now I want to use the table returned by the stored procedure here.
    SELECT [col1],[col2] FROM @Proc1Result

END

I tried using INSERT INTO @Proc1Result EXEC Proc2 (with parameters passed), but INSERT EXEC cannot be called in a nested statement.

Is there any way of accomplishing this? The environment is SQL Server 2008.

Upvotes: 8

Views: 49949

Answers (3)

Mark SQLDev
Mark SQLDev

Reputation: 539

One syntax for getting results into one sp from another is:

INSERT INTO [myTable]
EXEC Proc1 [param1], [param2], [param3], etc.

But you have to create the table first that it inserts into and the field names and types have to exactly match.

Upvotes: 2

Remus Rusanu
Remus Rusanu

Reputation: 294407

You're right, INSERT ... EXEC cannot nest. From How to Share Data Between Stored Procedures.

It can't nest. If some_sp tries to call some_other_sp with INSERT-EXEC, you will get an error message. Thus, you can only have one INSERT-EXEC active at a time. This is a restriction in SQL Server.

You need to find some other way. Erland's article linked goes trough pretty much all the options you have and discusses them at great detail.

Upvotes: 5

Abe Miessler
Abe Miessler

Reputation: 85116

You can nest stored procedures up to 32 levels.

I would recommend reading over this article regarding INSERT-EXEC. Here is a snippit:

If some_sp tries to call some_other_sp with INSERT-EXEC, you will get an error message. Thus, you can only have one INSERT-EXEC active at a time. This is a restriction in SQL Server.

Upvotes: 12

Related Questions