|
|
Error Message
Server: Msg 483, Level 16, State 2, Line 1
The OUTPUT clause cannot be used in an INSERT...EXEC
statement.
Causes
The OUTPUT clause, introduced in SQL Server 2005, returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE or MERGE statement.
|
These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. The results can also be inserted into a table or table variable. Additionally, you can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view.
One of the restrictions when using the OUTPUT clause together with the INSERT statement is that it cannot contain an EXECUTE statement; otherwise this error message will be encountered.
To illustrate how this error is generated, given the following tables structures and stored procedure:
CREATE TABLE [dbo].[Student] (
[StudentID] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
[FirstName] VARCHAR(50),
[LastName] VARCHAR(50),
[Email] VARCHAR(100)
)
GO
CREATE TABLE [dbo].[NewStudent] (
[StudentID] INT,
[FirstName] VARCHAR(50),
[LastName] VARCHAR(50),
[Email] VARCHAR(100)
)
GO
CREATE TABLE [dbo].[StudentCandidate] (
[CandidateID] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
[FirstName] VARCHAR(50),
[LastName] VARCHAR(50),
[Email] VARCHAR(100),
[GPA] DECIMAL(6, 3)
)
GO
CREATE PROCEDURE [dbo].[usp_GetStudentCandidates]
@MinimumGPA DECIMAL(6, 3)
AS
SELECT [FirstName], [LastName], [Email]
FROM [dbo].[StudentCandidate]
WHERE [GPA] >= @MinimumGPA
GO
The stored procedure returns a list of student candidates whose GPA is higher than the minimum GPA required by the school. The following script calls the stored procedure using the INSERT INTO… EXECUTE statement to insert students who meet the criteria into the [dbo].[Student] table. At the same time, these new students are also inserted into the [dbo].[NewStudent] table using the OUTPUT INTO clause:
INSERT INTO [dbo].[Student] ( [FirstName], [LastName], [Email] )
OUTPUT [inserted].[StudentID], [inserted].[FirstName], [inserted].[LastName], [inserted].[Email]
INTO [dbo].[NewStudent]
EXECUTE [dbo].[usp_GetStudentCandidates] 3.5
But since the list of qualified students are retrieved and created using INSERT INTO… EXECUTE statement together with the OUTPUT INTO clause, the following error message is generated:
Msg 483, Level 16, State 2, Line 4
The OUTPUT clause cannot be used in an INSERT...EXEC statement.
Solution / Work Around
As mentioned earlier, and as the error message suggests, the OUTPUT clause cannot be used in conjunction with the INSERT… EXECUTE statement. To overcome this restriction, one thing that can be done is to insert the qualified students into a temporary table or table variable first using the INSERT… EXECUTE statement without the OUTPUT clause. Then these new student records can now be inserted into student table as well as the new students table.
The following script can be used to perform the task of inserting the qualified students on both the students table as well as the new students table using the stored procedure that returns the qualified student candidates.
DECLARE @NewStudent TABLE (
[FirstName] VARCHAR(50),
[LastName] VARCHAR(50),
[Email] VARCHAR(100)
)
INSERT INTO @NewStudent ( [FirstName], [LastName], [Email] )
EXECUTE [dbo].[usp_GetStudentCandidates] 3.5
INSERT INTO [dbo].[Student] ( [FirstName], [LastName], [Email] )
OUTPUT [inserted].[StudentID], [inserted].[FirstName], [inserted].[LastName], [inserted].[Email]
INTO [dbo].[NewStudent]
SELECT [FirstName], [LastName], [Email]
FROM @NewStudent
|