127

I have three stored procedures Sp1, Sp2 and Sp3.

The first one (Sp1) will execute the second one (Sp2) and save returned data into @tempTB1 and the second one will execute the third one (Sp3) and save data into @tempTB2.

If I execute the Sp2 it will work and it will return me all my data from the Sp3, but the problem is in the Sp1, when I execute it it will display this error:

INSERT EXEC statement cannot be nested

I tried to change the place of execute Sp2 and it display me another error:

Cannot use the ROLLBACK statement within an INSERT-EXEC statement.

SchmitzIT
  • 9,227
  • 9
  • 65
  • 92
HAJJAJ
  • 3,667
  • 14
  • 42
  • 70

14 Answers14

125

This is a common issue when attempting to 'bubble' up data from a chain of stored procedures. A restriction in SQL Server is you can only have one INSERT-EXEC active at a time. I recommend looking at How to Share Data Between Stored Procedures which is a very thorough article on patterns to work around this type of problem.

For example a work around could be to turn Sp3 into a Table-valued function.

Eddie Groves
  • 33,851
  • 14
  • 47
  • 48
  • 11
    do you have any idea what the technical reason is for not allowing it? I can't find any info on this. – jtate Feb 15 '19 at 21:29
  • 2
    Unfortunately this is very often not an option. Many types of important information is only *reliably* available from system *stored procedures* (because in certain cases the respective management view contain unreliable/obsolete data; an example is the information returned by `sp_help_jobactivity`). – GSerg Jun 10 '20 at 08:08
25

This is the only "simple" way to do this in SQL Server without some giant convoluted created function or executed sql string call, both of which are terrible solutions:

  1. create a temp table
  2. openrowset your stored procedure data into it

EXAMPLE:

INSERT INTO #YOUR_TEMP_TABLE
SELECT * FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off EXEC [ServerName].dbo.[StoredProcedureName] 1,2,3')

Note: You MUST use 'set fmtonly off', AND you CANNOT add dynamic sql to this either inside the openrowset call, either for the string containing your stored procedure parameters or for the table name. Thats why you have to use a temp table rather than table variables, which would have been better, as it out performs temp table in most cases.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Mitch Stokely
  • 259
  • 3
  • 2
  • It's not a must to use SET FMTONLY OFF. You can just add an IF(1=0) which returns an empty table with the same data types that the procedure normally returns. – Guillermo Gutiérrez Aug 06 '13 at 00:29
  • 3
    Temp Tables and Table variables store their data differently.Table variables are supposed to be used for small results sets as the query optimizer does not maintain statistics on table variables. So for large data sets it is almost always better to use Temp tables. Here is a nice blog article on it http://www.mssqltips.com/sqlservertip/2825/sql-server-temp-table-vs-table-variable-performance-testing/ – gh9 Jan 25 '14 at 21:59
  • @gh9 yes, but this is a horrible idea for large result sets anyway. The statistics and use of an actual table in the temp database can cause significant overhead. I have a procedure that returns a recordset with 1 row of current values (querying several tables) and a procedure that stores that in a table variable and compares it to values in another table with the same format. Changing from a temp table to a table variable sped the average time up from 8ms to 2ms, which is important when it's called several times a second throughout the day and 100,000 times in a nightly process. – Jason Goemaat Aug 04 '14 at 21:06
  • 2
    Why would you want statistics to be maintained on a table variable? The whole point is to create a temporary table in RAM that will be destroyed after the query is finished. By definition any statistics created on such a table would never be used. Generally, the fact that data in a table variable remains in RAM wherever possible makes them faster than Temp Tables in any scenario where your data is smaller than the amount of RAM available to SQL Server (which in these days of 100GB+ memory pools for our SQL Servers, is almost always) – Geoff Griswald Jan 06 '20 at 12:09
  • This doesn't work for extended stored procedures though. The error is *The metadata could not be determined because statement 'EXECUTE @retval OUTPUT' in procedure ...' invokes an extended stored procedure*. – GSerg Jun 10 '20 at 08:26
17

OK, encouraged by jimhark here is an example of the old single hash table approach: -

CREATE PROCEDURE SP3 as

BEGIN

    SELECT 1, 'Data1'
    UNION ALL
    SELECT 2, 'Data2'

END
go


CREATE PROCEDURE SP2 as

BEGIN

    if exists (select  * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#tmp1'))
        INSERT INTO #tmp1
        EXEC SP3
    else
        EXEC SP3

END
go

CREATE PROCEDURE SP1 as

BEGIN

    EXEC SP2

END
GO


/*
--I want some data back from SP3

-- Just run the SP1

EXEC SP1
*/


/*
--I want some data back from SP3 into a table to do something useful
--Try run this - get an error - can't nest Execs

if exists (select  * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#tmp1'))
    DROP TABLE #tmp1

CREATE TABLE #tmp1 (ID INT, Data VARCHAR(20))

INSERT INTO #tmp1
EXEC SP1


*/

/*
--I want some data back from SP3 into a table to do something useful
--However, if we run this single hash temp table it is in scope anyway so
--no need for the exec insert

if exists (select  * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#tmp1'))
    DROP TABLE #tmp1

CREATE TABLE #tmp1 (ID INT, Data VARCHAR(20))

EXEC SP1

SELECT * FROM #tmp1

*/
Matt Luckham
  • 431
  • 4
  • 4
  • I used this work-around as well. Thank you for the idea! – SQL_Guy Jun 10 '19 at 17:24
  • Fantastic work-around. This helped me learn more about temp table scoping. EG, I didn't realize you can use a temp table in a dynsql string if it was declared outside it. Similar concept here. Thanks a lot. – jbd Aug 24 '19 at 00:33
12

My work around for this problem has always been to use the principle that single hash temp tables are in scope to any called procs. So, I have an option switch in the proc parameters (default set to off). If this is switched on, the called proc will insert the results into the temp table created in the calling proc. I think in the past I have taken it a step further and put some code in the called proc to check if the single hash table exists in scope, if it does then insert the code, otherwise return the result set. Seems to work well - best way of passing large data sets between procs.

Matt Luckham
  • 431
  • 4
  • 4
  • 3
    I like this answer and I'd bet you'll get more up votes if you were to provide and example. – jimhark May 13 '16 at 00:32
  • I've been doing this for years. Is it still necessary in SQL Azure though? – Nick Allan Nov 29 '19 at 15:14
  • 1
    yes, Azure uses the exact same engine as SQL Server the only real difference is storage and CPU – Geoff Griswald Oct 20 '20 at 16:29
  • That's assuming you have a Managed Instance of SQLServer. I have found Azure SQL Server is quite different. E.g. a cross-database query necessitates an EXTERNAL DATASOURCE at the very least. But I've written a lot of code since my comment above using the workaround described without any issue. – Nick Allan Nov 16 '20 at 09:37
7

This trick works for me.

You don't have this problem on remote server, because on remote server, the last insert command waits for the result of previous command to execute. It's not the case on same server.

Profit that situation for a workaround.

If you have the right permission to create a Linked Server, do it. Create the same server as linked server.

  • in SSMS, log into your server
  • go to "Server Object
  • Right Click on "Linked Servers", then "New Linked Server"
  • on the dialog, give any name of your linked server : eg: THISSERVER
  • server type is "Other data source"
  • Provider : Microsoft OLE DB Provider for SQL server
  • Data source: your IP, it can be also just a dot (.), because it's localhost
  • Go to the tab "Security" and choose the 3rd one "Be made using the login's current security context"
  • You can edit the server options (3rd tab) if you want
  • Press OK, your linked server is created

now your Sql command in the SP1 is

insert into @myTempTable
exec THISSERVER.MY_DATABASE_NAME.MY_SCHEMA.SP2

Believe me, it works even you have dynamic insert in SP2

ainasiart
  • 382
  • 2
  • 10
  • It's a hack, but depending on your point of view so is the accepted/most-upvoted answer. For now, I just need to be able to execute my SP in ADO.NET, but I have this workaround noted for anyone who may want to use the SP within the DB layer. – KeithS May 25 '21 at 20:26
  • 1
    returns Error : Table-valued parameters are not allowed in remote calls between servers. – R.Akhlaghi Oct 13 '21 at 11:16
6

I found a work around is to convert one of the prods into a table valued function. I realize that is not always possible, and introduces its own limitations. However, I have been able to always find at least one of the procedures a good candidate for this. I like this solution, because it doesn't introduce any "hacks" to the solution.

dakab
  • 5,379
  • 9
  • 43
  • 67
Roman K
  • 394
  • 4
  • 5
  • but a disadvantage is a problem with exception handling if the function is complex, right ? – Muflix Feb 03 '17 at 11:18
5

I encountered this issue when trying to import the results of a Stored Proc into a temp table, and that Stored Proc inserted into a temp table as part of its own operation. The issue being that SQL Server does not allow the same process to write to two different temp tables at the same time.

The accepted OPENROWSET answer works fine, but I needed to avoid using any Dynamic SQL or an external OLE provider in my process, so I went a different route.

One easy workaround I found was to change the temporary table in my stored procedure to a table variable. It works exactly the same as it did with a temp table, but no longer conflicts with my other temp table insert.

Just to head off the comment I know that a few of you are about to write, warning me off Table Variables as performance killers... All I can say to you is that in 2020 it pays dividends not to be afraid of Table Variables. If this was 2008 and my Database was hosted on a server with 16GB RAM and running off 5400RPM HDDs, I might agree with you. But it's 2020 and I have an SSD array as my primary storage and hundreds of gigs of RAM. I could load my entire company's database to a table variable and still have plenty of RAM to spare.

Table Variables are back on the menu!

Geoff Griswald
  • 937
  • 12
  • 24
2

I recommend to read this entire article. Below is the most relevant section of that article that addresses your question:

Rollback and Error Handling is Difficult

In my articles on Error and Transaction Handling in SQL Server, I suggest that you should always have an error handler like

BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   EXEC error_handler_sp
   RETURN 55555
END CATCH

The idea is that even if you do not start a transaction in the procedure, you should always include a ROLLBACK, because if you were not able to fulfil your contract, the transaction is not valid.

Unfortunately, this does not work well with INSERT-EXEC. If the called procedure executes a ROLLBACK statement, this happens:

Msg 3915, Level 16, State 0, Procedure SalesByStore, Line 9 Cannot use the ROLLBACK statement within an INSERT-EXEC statement.

The execution of the stored procedure is aborted. If there is no CATCH handler anywhere, the entire batch is aborted, and the transaction is rolled back. If the INSERT-EXEC is inside TRY-CATCH, that CATCH handler will fire, but the transaction is doomed, that is, you must roll it back. The net effect is that the rollback is achieved as requested, but the original error message that triggered the rollback is lost. That may seem like a small thing, but it makes troubleshooting much more difficult, because when you see this error, all you know is that something went wrong, but you don't know what.

spencer741
  • 965
  • 1
  • 10
  • 22
  • 4
    How is this an answer to the question? – Rab May 11 '21 at 11:11
  • 1
    It directly gives information on why Insert-exec does not work in every case, especially when bubbling errors and handling rollbacks. Now, the OP already has an answer flagged, but my answer helps to provide additional concrete info with an extremely relevant source on the matter. – spencer741 May 11 '21 at 11:17
  • may or may not be an answer to this question but it was helpful to me to solve the the issue I had where I had the first error message. Moved it to a UDF like the link suggested and it worked. – Brian Edwards May 20 '21 at 15:08
1

I had the same issue and concern over duplicate code in two or more sprocs. I ended up adding an additional attribute for "mode". This allowed common code to exist inside one sproc and the mode directed flow and result set of the sproc.

phoenixAZ
  • 429
  • 3
  • 17
1

what about just store the output to the static table ? Like

-- SubProcedure: subProcedureName
---------------------------------
-- Save the value
DELETE lastValue_subProcedureName
INSERT INTO lastValue_subProcedureName (Value)
SELECT @Value
-- Return the value
SELECT @Value

-- Procedure
--------------------------------------------
-- get last value of subProcedureName
SELECT Value FROM lastValue_subProcedureName

its not ideal, but its so simple and you don't need to rewrite everything.

UPDATE: the previous solution does not work well with parallel queries (async and multiuser accessing) therefore now Iam using temp tables

-- A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished. 
-- The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. 
-- The table cannot be referenced by the process that called the stored procedure that created the table.
IF OBJECT_ID('tempdb..#lastValue_spGetData') IS NULL
CREATE TABLE #lastValue_spGetData (Value INT)

-- trigger stored procedure with special silent parameter
EXEC dbo.spGetData 1 --silent mode parameter

nested spGetData stored procedure content

-- Save the output if temporary table exists.
IF OBJECT_ID('tempdb..#lastValue_spGetData') IS NOT NULL
BEGIN
    DELETE #lastValue_spGetData
    INSERT INTO #lastValue_spGetData(Value)
    SELECT Col1 FROM dbo.Table1
END

 -- stored procedure return
 IF @silentMode = 0
 SELECT Col1 FROM dbo.Table1
Muflix
  • 6,192
  • 17
  • 77
  • 153
  • Generally, you cannot create a SProc ad-hoc like you can with Tables. You will need to expand on your example with more references, as this approach is not really readily known or accepted. Also, it resembles a Lambda Expression more than a SProc execution, which ANSI-SQL does not allow for Lambda Expression approaches. – GoldBishop Apr 27 '17 at 12:23
  • It works but I found that its not work well with parallel queries (async and multi user accesses) either. Therefore now Iam using temp table approach. I updated my answer. – Muflix Apr 27 '17 at 14:06
  • 1
    The Temp table logic is good, it was the SProc reference that I was concerned with. Sproc's can not inherently be queried from directly. Table-Valued Functions can be directly queried from. Must like you alluded to in your updated logic, the best approach is a Temp Table, session, instance, or global, and operate from that point. – GoldBishop May 10 '17 at 13:24
0

Declare an output cursor variable to the inner sp :

@c CURSOR VARYING OUTPUT

Then declare a cursor c to the select you want to return. Then open the cursor. Then set the reference:

DECLARE c CURSOR LOCAL FAST_FORWARD READ_ONLY FOR 
SELECT ...
OPEN c
SET @c = c 

DO NOT close or reallocate.

Now call the inner sp from the outer one supplying a cursor parameter like:

exec sp_abc a,b,c,, @cOUT OUTPUT

Once the inner sp executes, your @cOUT is ready to fetch. Loop and then close and deallocate.

demongolem
  • 9,474
  • 36
  • 90
  • 105
0

If you are able to use other associated technologies such as C#, I suggest using the built in SQL command with Transaction parameter.

var sqlCommand = new SqlCommand(commandText, null, transaction);

I've created a simple Console App that demonstrates this ability which can be found here: https://github.com/hecked12/SQL-Transaction-Using-C-Sharp

In short, C# allows you to overcome this limitation where you can inspect the output of each stored procedure and use that output however you like, for example you can feed it to another stored procedure. If the output is ok, you can commit the transaction, otherwise, you can revert the changes using rollback.

spidernet12
  • 27
  • 1
  • 9
0

In my case, I was calling SP1 into SP2 where Insert into #temptable is available and further the output of SP2 I tried to insert into #temtable2 due to which "an insert exec statement cannot be nested" error poped up.

I fixed the issue by placing the final #temptable insertion inside the SP2 itself. So that If we call SP2, it will insert the data into #temptable2 at the end of the execution hene no additional INSERT INTO is not needed outside the execution.

I'm Answering this assuming someone like me will get assistance from his answer.

Sathiya Kumar V M
  • 507
  • 12
  • 34
-1

On SQL Server 2008 R2, I had a mismatch in table columns that caused the Rollback error. It went away when I fixed my sqlcmd table variable populated by the insert-exec statement to match that returned by the stored proc. It was missing org_code. In a windows cmd file, it loads result of stored procedure and selects it.

set SQLTXT= declare @resets as table (org_id nvarchar(9), org_code char(4), ^
tin(char9), old_strt_dt char(10), strt_dt char(10)); ^
insert @resets exec rsp_reset; ^
select * from @resets;

sqlcmd -U user -P pass -d database -S server -Q "%SQLTXT%" -o "OrgReport.txt"
user3448451
  • 21
  • 1
  • 2
  • OP was asking about an error that occurs when using insert-exec statements in nested stored procedures. Your issue would return a different error, such as "The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns." – Losbear Jan 10 '17 at 21:54
  • This is more of a warning that it's possible to get this message erroneously. – user3448451 Mar 09 '17 at 05:53