Avoid nested INSERT EXEC (where possible)

Some days ago, I received a tedious error while executing a pipeline of nested stored procedures, which contains INSERT-EXEC statements. The issue is based on a SQL Server limitation, by design since SQL Server 2005. Actually, the INSERT-EXEC was supported in SQL Server 2000, but I’ve never tried that behavior on the older version. Let’s see the behavior.

Scenario

Suppose to have a stored procedure that returns data, like the following one:

CREATE PROCEDURE dbo.proc_getdata
AS
 SELECT 1 AS Value
 UNION
 SELECT 2
GO

Now, we want to store those two records into a temporary table, with another stored procedure:

CREATE PROCEDURE dbo.proc_insert_TopLevel
AS

CREATE TABLE #first (val int);

-- first insert exec
 INSERT INTO #first (val)
 EXEC dbo.proc_getdata;

 SELECT
 val
 FROM
 #first;
GO

As we can see, the module inserts the data returned from the dbo.proc_getdata. Now, let’s try to create another stored procedure with the same behavior, but which calls the dbo.proc_insert_TopLevel:

CREATE PROCEDURE dbo.proc_insert_Nested
AS

CREATE TABLE #second (val int);

-- nested insert exec (will throw exception)
 INSERT INTO #second (val)
 EXEC dbo.proc_insert_TopLevel;

SELECT
 val
 FROM
 #second;
GO

We called the first “insert” stored procedure dbo.proc_insert_TopLevel and the second dbo.proc_insert_Nested. Executing the dbo.proc_insert_Nested sp, we get the following error message:

The scenario above has a simple workaround. In that case, a possible solution can be to replace the first stored procedure with an inline or multi-statement table function in order to remove the first INSERT-EXEC:

CREATE FUNCTION dbo.getdata()
RETURNS TABLE AS
RETURN
(
 SELECT 1 AS id
 UNION
 SELECT 2
);
GO

CREATE PROCEDURE dbo.proc_insert_TopLevel
AS

CREATE TABLE #first (val int);

-- first insert exec
INSERT INTO #first (val)
SELECT Id FROM dbo.getdata()

SELECT
val
FROM
#first;
GO

That quick sample removes the nested INSERT-EXEC statements. But real scenarios are often more complicated than the described one.

Conclusions

The scenario above was very simple and the solution covers a small number of cases. We can find a lot of situations in which this workaround cannot be applied. For instance:

• The “data source” needs to be included in a stored procedure (with data changes, not allowed in functions)
• The number of nested operations is more than one
• The data source cannot be refactored (it could be a third party stored procedure)
• The callee is a system stored procedure (we cannot change it)

So, the restriction cannot be avoided simply. We can think about storing data in temporary tables (i.e. global ones), but in some cases, it’s not possible to workaround. For more details on “how to share data between stored procedures“, you can read this post. Additionally, there are some items on MS Connect:

• https://connect.microsoft.com/SQLServer/feedback/details/272133/cannot-have-nested-insert-exec http://connect.microsoft.com/SQLServer/feedback/details/294571/improve-insert-exec
• http://connect.microsoft.com/SQLServer/feedback/details/525126/an-insert-exec-statement-cannot-be-nested

Stay Tuned! 🙂