Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Error: INSERT statement with OUTPUT clause? Expand / Collapse
Author
Message
Posted Thursday, March 27, 2008 12:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, December 12, 2011 3:42 AM
Points: 103, 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
Post #475243
Posted Thursday, March 27, 2008 1:05 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 22, 2010 12:25 PM
Points: 16, Visits: 61
no need to put the columns for default value . It takes care by sql server.

Alkesh Khedle
Post #475251
Posted Thursday, March 27, 2008 7:05 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:31 PM
Points: 14,000, Visits: 28,381
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #475366
Posted Thursday, March 27, 2008 7:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:39 PM
Points: 7,152, Visits: 15,634
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?
Post #475375
Posted Thursday, March 27, 2008 7:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:31 PM
Points: 14,000, Visits: 28,381
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #475389
Posted Thursday, February 23, 2012 2:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, October 19, 2014 1:27 AM
Points: 5, Visits: 528
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.
Post #1256939
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse