|
|
SQL Server Error Messages - Msg 8164
Error Message
Server: Msg 8164, Level 16, State 1, Line 1
An INSERT EXEC statement cannot be nested.
Causes
This error occurs when calling a stored procedure and inserting the result of the stored procedure into a table or table variable (INSERT ... EXECUTE) and the stored procedure being called already contains an INSERT ... EXECUTE statement within its body.
|
To illustrate this scenario, here’s a script that will show how this error is generated:
CREATE TABLE [dbo].[Student] (
[StudentID] INT NOT NULL PRIMARY KEY,
[StudentName] VARCHAR(100)
)
CREATE TABLE [dbo].[Subject] (
[SubjectID] INT NOT NULL PRIMARY KEY,
[SubjectName] VARCHAR(100)
)
CREATE TABLE [dbo].[StudentSubject] (
[StudentSubjectID] INT NOT NULL PRIMARY KEY,
[StudentID] INT REFERENCES [dbo].[Student] ( [StudentID] ),
[SubjectID] INT REFERENCES [dbo].[Subject] ( [SubjectID] )
)
CREATE TABLE [dbo].[StudentGrade] (
[StudentSubjectID] INT REFERENCES [dbo].[StudentSubject] ( [StudentSubjectID] ),
[Grade] DECIMAL(4, 1)
)
GO
CREATE PROCEDURE [dbo].[usp_SearchStudents]
@SubjectID INT
AS
SET NOCOUNT ON
SELECT B.[StudentID], B.[StudentName]
FROM [dbo].[StudentSubject] A INNER JOIN [dbo].[Student] B
ON A.[StudentID] = B.[StudentID]
WHERE A.[SubjectID] = @SubjectID
GO
CREATE PROCEDURE [dbo].[usp_GetStudentGrades]
@SubjectID INT
AS
SET NOCOUNT ON
DECLARE @Students TABLE (
[StudentID] INT,
[StudentName] VARCHAR(50)
)
INSERT INTO @Students ( [StudentID], [StudentName] )
EXECUTE [dbo].[usp_SearchStudents] @SubjectID
SELECT A.[StudentID], A.[StudentName], C.[Grade]
FROM @Students A INNER JOIN [dbo].[StudentSubject] B
ON A.[StudentID] = B.[StudentID]
INNER JOIN [dbo].[StudentGrade] C
ON B.[StudentSubjectID] = C.[StudentSubjectID]
GO
DECLARE @StudentGrades TABLE (
[StudentID] INT,
[StudentName] VARCHAR(100),
[Grade] DECIMAL(4, 1)
)
INSERT INTO @StudentGrades ( [StudentID], [StudentName], [Grade] )
EXECUTE [dbo].[usp_GetStudentGrades] 1
GO
The last statement (INSERT INTO @StudentGrades) will generate the following error message:
Msg 8164, Level 16, State 1, Procedure usp_GetStudentGrades, Line 10
An INSERT EXEC statement cannot be nested.
As can be seen from this script, the second stored procedure ([dbo].[usp_GetStudentGrades]) is calling the first stored procedure ([dbo].[usp_SearchStudents]) and inserts the result set returned into a table variable (@Students). The second stored procedure is thus performing an INSERT EXECUTE statement pair. Calling the second stored procedure and inserting the result into a table variable will generate this error. However, calling the second procedure without inserting the result into a table or table variable will not generate this error.
Solution / Work Around:
Option 1: Merge First Stored Procedure with the Second Stored Procedure
There are three ways to work around this error. The first option is to integrate the two stored procedures together into a single stored procedure. This option is possible if there are no other stored procedures, scripts or applications that are using either of the stored procedures to be merged.
Here’s how the combined stored procedure will look like:
-- Option #1 : Merge First Stored Procedure with the Second Stored Procedure
CREATE PROCEDURE [dbo].[usp_GetStudentGrades]
@SubjectID INT
AS
DECLARE @Students TABLE (
[StudentID] INT,
[StudentName] VARCHAR(50)
)
INSERT INTO @Students ( [StudentID], [StudentName] )
SELECT B.[StudentID], B.[StudentName]
FROM [dbo].[StudentSubject] A INNER JOIN [dbo].[Student] B
ON A.[StudentID] = B.[StudentID]
WHERE A.[SubjectID] = @SubjectID
SELECT A.[StudentID], A.[StudentName], C.[Grade]
FROM @Students A INNER JOIN [dbo].[StudentSubject] B
ON A.[StudentID] = B.[StudentID]
INNER JOIN [dbo].[StudentGrade] C
ON B.[StudentSubjectID] = C.[StudentSubjectID]
GO
And here’s how the new stored procedure is called without generating this error message:
DECLARE @StudentGrades TABLE (
[StudentID] INT,
[StudentName] VARCHAR(100),
[Grade] DECIMAL(4, 1)
)
INSERT INTO @StudentGrades ( [StudentID], [StudentName], [Grade] )
EXECUTE [dbo].[usp_GetStudentGrades] 1
GO
Option 2: Convert Stored Procedure to a Table-Valued Function
The second option is to convert the first stored procedure into a table-valued function, either as an inline table-valued function or multi-statement table-valued function. A table-valued user-defined function can be used to replace stored procedures that return a single result set.
Here’s how the inline table-valued user-defined function will look like for the first stored procedure earlier, which returns all students for a particular subject:
-- Option #2 : Convert Stored Procedure to a Table-Valued Function
CREATE FUNCTION [dbo].[ufn_SearchStudents] (
@SubjectID INT
)
RETURNS TABLE
AS
RETURN (
SELECT B.[StudentID], B.[StudentName]
FROM [dbo].[StudentSubject] A INNER JOIN [dbo].[Student] B
ON A.[StudentID] = B.[StudentID]
WHERE A.[SubjectID] = @SubjectID
)
GO
Now that the first stored procedure is a table-valued function, it can be called from the second stored procedure without using the INSERT … EXECUTE statement pair. Here’s how the second stored procedure will look like calling the inline table-valued user-defined function:
CREATE PROCEDURE [dbo].[usp_GetStudentGrades]
@SubjectID INT
AS
SET NOCOUNT ON
SELECT A.[StudentID], A.[StudentName], C.[Grade]
FROM [dbo].[ufn_SearchStudents] ( @SubjectID ) A
INNER JOIN [dbo].[StudentSubject] B
ON A.[StudentID] = B.[StudentID]
INNER JOIN [dbo].[StudentGrade] C
ON B.[StudentSubjectID] = C.[StudentSubjectID]
GO
Calling the updated second stored procedure using the INSERT… EXECUTE statement pair will not generate the error anymore:
DECLARE @StudentGrades TABLE (
[StudentID] INT,
[StudentName] VARCHAR(100),
[Grade] DECIMAL(4, 1)
)
INSERT INTO @StudentGrades ( [StudentID], [StudentName], [Grade] )
EXECUTE [dbo].[usp_GetStudentGrades] 1
GO
Option 3 - Use OPENROWSET
The third option in avoiding this error message is with the use of the OPENROWSET function. The OPENROWSET function is an alternative method to accessing tables in a linked server and is a one-time, ad-hoc method of connecting and accessing remote data by using OLE DB. The OPENROWSET function can be referenced in the FROM clause of a query as if it was a table name. Although the main use of the OPENROWSET function is to connect and access data from a remote server, it can also be used to access data on the local server.
Using the OPENROWSET function to work around this error, the stored procedures need not be changed. The only thing that will be changed is on how the second stored procedure, which contains the INSERT...EXECUTE statement pair, will be called.
As can be seen in the following script, the second stored procedure is not called using the INSERT...EXECUTE statement pair but with the use of the OPENROWSET function. The call to execute the second stored procedure is passed as a parameter to the OPENROWSET function.
-- Option #3 : Use OPENROWSET
DECLARE @StudentGrades TABLE (
[StudentID] INT,
[StudentName] VARCHAR(100),
[Grade] DECIMAL(4, 1)
)
INSERT INTO @StudentGrades ( [StudentID], [StudentName], [Grade] )
SELECT A.*
FROM OPENROWSET('SQLNCLI', 'Server=.;Database=SQL2008;Uid=sshelper@123;Pwd=sshelper@123',
'EXECUTE [dbo].[usp_GetStudentGrades] 1 ') AS a
SELECT * FROM @StudentGrades
One disadvantage of the OPENROWSET method is the exposure of the user name and password to the database where the data is retrieved.
|