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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply