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.
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:Msg 8164, Level 16, State 1, Procedure proc_insert_TopLevel, Line 9 An INSERT EXEC statement cannot be nested.
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.
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
Stay Tuned! 🙂