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

Question

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.

Answer 1

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.