March 27, 2008 at 12:37 am
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
March 27, 2008 at 1:05 am
no need to put the columns for default value . It takes care by sql server.
Alkesh Khedle
March 27, 2008 at 7:05 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 27, 2008 at 7:14 am
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?
March 27, 2008 at 7:29 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 23, 2012 at 2:09 pm
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.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy