Error: INSERT statement with OUTPUT clause?

  • 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

  • no need to put the columns for default value . It takes care by sql server.

    Alkesh Khedle

  • 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

  • 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?

  • 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

  • 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