So I have declared a query within a varchar variable like:
DECLARE @sql VARCHAR(MAX) = 'select * from table'
I've also declared a temp table @Results that matches the column structure of the query.
I then call:
INSERT INTO @Results
EXEC (@sql)
The error I get is:
An INSERT EXEC statement cannot be nested.
I would also like to add that the query executes fine without the part
INSERT INTO @Results
What could be causing it? Any tips will be greatly appreciated
You can't stack together more than 1 INSERT INTO + EXEC, this is a limitation from SQL Server.
DECLARE @Test TABLE (Number INT)
INSERT INTO @Test (Number)
EXEC ('
CREATE TABLE #InnerTable (SomeColumn INT);
INSERT INTO #InnerTable (SomeColumn)
EXEC (''SELECT 1'');
SELECT 1;')
Msg 8164, Level 16, State 1, Line 4 An INSERT EXEC statement cannot be nested.
If we remove the inner INSERT INTO + EXEC...
DECLARE @Test TABLE (Number INT)
INSERT INTO @Test (Number)
EXEC ('
CREATE TABLE #InnerTable (SomeColumn INT);
SELECT 1;')
Succeed!
There are multiple ways to workaround this limitation, however most of them will require modifying the EXEC content. You can find an exhaustive explanation here. The one I usually use with my procedures is sharing a temporary table. You need to create a temporary table outside the EXEC then load it inside. The table is still accessible outside the EXEC scope because it was created outside.
IF OBJECT_ID('tempdb..#Test') IS NOT NULL
DROP TABLE #Test
CREATE TABLE #Test (Number INT)
EXEC ('INSERT INTO #Test (Number) SELECT 1')
SELECT * FROM #Test
Downside of this approach is that the EXEC part might fail if the temporary table wasn't created or it was created with wrong column names or data types. Keep in mind that this won't work with variable tables, they need to be temporary (at least) to be accessible inside the EXEC scope.
*in your select statement so you can see whether the data is inserted into your@Resultsin the correct column orderINSERT INTO + EXEC. Your Dynamic SQL probably has one already, so this is why the engine is telling you that you can't nest them. I'd recommend reading this paper on all different ways to share data between procedures sommarskog.se/share_data.html . Personally I use point 4.1 (sharing temp table) in most of our projects.insert table exec someStoredProcedureand the stored procedure is trying to insert into a temp table internally.