Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Error: INSERT statement with OUTPUT clause?


Error: INSERT statement with OUTPUT clause?

Author
Message
Hemant Kumar
Hemant Kumar
SSC-Enthusiastic
SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)

Group: General Forum Members
Points: 111 Visits: 28
Hello Everyone,
I have the following stored procedure that makes use of the OUTPUT clause to return the IDENTITY inserted.

CREATE PROCEDURE [dbo].[Employee_Add] 
@CategoryID Int,
@EmpName varchar(10),
@CreatorUserID Int,
@SenderUserID Int
AS
BEGIN
SET NOCOUNT ON;

INSERT INTO Empl OUTPUT INSERTED.ID
( CategoryID, EmpName, CreatorUserID, SenderUserID, CreatedDate,
LastModifiedDate, IsDeleted )
VALUES
( @CategoryID, @EmpName, @CreatorUserID, @SenderUserID, DEFAULT,
DEFAULT, DEFAULT )

END



When executing the above procedure, I get the following error. The columns CreatedDate, LastModifiedDate and IsDeleted have default values set to them.

Msg 4121, Level 16, State 1, Procedure Employee_Add, Line 15
Cannot find either column "INSERTED" or the user-defined function or aggregate "INSERTED.ID", or the name is ambiguous.


Please help me fix this issue
Alkesh Khedle
Alkesh Khedle
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 61
no need to put the columns for default value . It takes care by sql server.

Alkesh Khedle
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19893 Visits: 32363
How about
OUTPUT INSERTED.CategoryId



because there wasn't a column in the list called ID

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Matt Miller (#4)
Matt Miller (#4)
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8349 Visits: 18275
The OUTPUT clause is in the wrong place, too. that's one of the causes of your error.

It goes AFTER the affected columns list.

CREATE PROCEDURE [dbo].[Employee_Add]
@CategoryID Int,
@EmpName varchar(10),
@CreatorUserID Int,
@SenderUserID Int
AS
BEGIN
SET NOCOUNT ON;

INSERT INTO Empl (
CategoryID, EmpName, CreatorUserID, SenderUserID, CreatedDate,
LastModifiedDate, IsDeleted )
OUTPUT INSERTED.ID --<-- OUTPUT goes here
VALUES
( @CategoryID, @EmpName, @CreatorUserID, @SenderUserID, DEFAULT,
DEFAULT, DEFAULT )
END



Now - the column names need to be the same as what's in the destination table, so Grant is probably right as well...

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19893 Visits: 32363
Oops. Missed that one. I hate when I get hung on the first error I see.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
harshavasa
harshavasa
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 642
try this :

CREATE PROCEDURE [dbo].[Employee_Add]
@CategoryID Int,
@EmpName varchar(10),
@CreatorUserID Int,
@SenderUserID Int
AS
BEGIN
SET NOCOUNT ON;

INSERT INTO Empl
( CategoryID, EmpName, CreatorUserID, SenderUserID, CreatedDate,
LastModifiedDate, IsDeleted )
OUTPUT INSERTED.categoryID
VALUES
( @CategoryID, @EmpName, @CreatorUserID, @SenderUserID, DEFAULT,
DEFAULT, DEFAULT )

END


Resolution: Output clause should be followed by the inserted.column name of the table. error occurred due to non existence of ID column.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search