Using the results of a nested stored procedure when invoking a Sql Server 2008 stored procedure

Question

Can I use the results of one stored procedure in another stored procedure?

those.

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 to use INSERT INTO @Proc1Result EXEC Proc2 (with parameters passed), but INSERT EXECcannot be called in a nested expression.

Is there any way to do this? The environment is SQL Server 2008.

Answer 1

You can nest stored procedures up to 32 levels .

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

some_sp some_other_sp INSERT-EXEC . , INSERT-EXEC . SQL Server.

Answer 2

, INSERT ... EXEC . .

. some_sp some_other_sp INSERT-EXEC, . , INSERT-EXEC . SQL Server.

. Erland , , .

Answer 3

sp :

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

, , .