Cannot get results from Stored Procedure that uses INSERT EXEC.

[JonathanHallKJR]

At the bottom is a tSQLt test that replicates the "INSERT EXEC statement cannot be nested.” issue.

This has been reported a while ago the consensus is this requires a CLR procedure to work around SQL server limitations.

• https://groups.google.com/d/msg/tsqlt/o8XGKG8UyM4/qFVVeHiIMvMJ
• https://groups.google.com/d/msg/tsqlt/O7VbCz6cgWg/9x2Ia8YCI5MJ

Replication

EXEC tSQLt.NewTestClass 'ReplicateInsertExecIssue';
GO

-- This proc returns a couple of rows
CREATE PROC ReplicateInsertExecIssue.[_DataReturnTable]
AS
	SELECT CAST('TEST' AS VARCHAR(15)) as col1, CAST(123 AS INT) AS col2 
	UNION SELECT 'TEST', 456
GO

-- This proc returns two tables after using INSERT EXEC
CREATE PROC ReplicateInsertExecIssue.[_DataUsesInsertExec]
AS 
BEGIN
	DECLARE @cachedResults TABLE(col1 VARCHAR(15) NULL , col2 int NULL);
	INSERT INTO @cachedResults EXEC ReplicateInsertExecIssue.[_DataReturnTable];
	SELECT col1, SUM(col2) AS col2 FROM @cachedResults GROUP BY col1;
	SELECT col1, MIN(col2) AS col2 FROM @cachedResults GROUP BY col1;
END;
GO

-- This tries to test the contents returned by the stored proc
CREATE PROC ReplicateInsertExecIssue.[test Replicate INSERT EXEC cannot be nested]
AS BEGIN	
	CREATE TABLE #expected (col1 VARCHAR(15) NULL , col2 int NULL);		
	INSERT INTO #expected values ('TEST', 123+456);

	CREATE TABLE #actual (col1 VARCHAR(15) NULL, col2 int NULL);
	INSERT INTO #actual EXEC tSQLt.ResultSetFilter 1, 'EXEC ReplicateInsertExecIssue._DataUsesInsertExec';

	EXEC tSQLt.AssertEqualsTable '#expected', '#actual';
END;
GO

EXEC tSQLt.Run 'ReplicateInsertExecIssue';
go