Calling a stored procedure from another procedure

Question

Using this table variable:

DECLARE @ReturnValue VARCHAR
DECLARE @OUT_MAIN_ERROR VARCHAR
DECLARE @Result VARCHAR(50)

BEGIN
    DECLARE @TableVariable TABLE (result VARCHAR(50))

    INSERT INTO @TableVariable  
        EXEC [dbo].[DRIVEPOOL2]

    SELECT result 
    FROM @TableVariable
END

Using temp table:

DECLARE @ReturnValue VARCHAR
DECLARE @OUT_MAIN_ERROR VARCHAR
DECLARE @Result VARCHAR(50)

BEGIN
    CREATE TABLE #kola(result VARCHAR(50))

    INSERT INTO #kola  
        EXEC [dbo].[DRIVEPOOL2]

    SELECT * 
    FROM #kola

    DROP TABLE #kola
END

I get error:

Msg 8164, Level 16, State 1, Procedure DRIVEPOOL2, Line 45 [Batch Start Line 3]
An INSERT EXEC statement cannot be nested.

I have tried with both temp table and table variable, both are throwing the error that the INSERT EXEC statement can't be nested.

Drive Pool Procedure for Reference - Github

[Bad habits to kick : declaring VARCHAR without (length)](https://sqlblog.org/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length) - you should **always** provide a length for any `varchar` variables and parameters that you use. If you define a variable with just `varchar` - then you get a variable that can hold **exactly ONE character** of data - typically *not* what you want! — marc_s, Jul 21 '18 at 06:07
`DRIVEPOOL2` what does it return? or it just has a select statement with one column? — Mani Deep, Jul 21 '18 at 07:01
Possible duplicate of [Errors: "INSERT EXEC statement cannot be nested." and "Cannot use the ROLLBACK statement within an INSERT-EXEC statement." How to solve this?](https://stackoverflow.com/questions/3795263/errors-insert-exec-statement-cannot-be-nested-and-cannot-use-the-rollback-s) — Mani Deep, Jul 21 '18 at 07:02

score 0 · Answer 1 · answered Jul 21 '18 at 10:16

Seems to be duplicate with this thread: INSERT EXEC Statement cannot be nested

This error occurs when calling a stored procedure and inserting the result of the stored procedure into a table or table variable (INSERT ... EXECUTE) and the stored procedure being called already contains an INSERT ... EXECUTE statement within its body.

Read more about this error here:http://www.sql-server-helper.com/error-messages/msg-8164.aspx

You can try OPENROWSET to overcome this problem

INSERT INTO @TableVariable ( col1, col2, col3,... )
SELECT SP.*
FROM OPENROWSET('SQLNCLI', '[Your connection string]','EXECUTE [dbo].[DRIVEPOOL2]') AS SP

Calling a stored procedure from another procedure

1 Answers1