CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Cameron Saliba
Cameron Saliba

Reputation: 179

Error with IDENT_CURRENT - Invalid column name

I am trying to update a column in my table which was last inserted. I tried creating this stored procedure: 

CREATE PROCEDURE [dbo].[msp_AssociateEvent]
(
    @EventId int
)

AS

UPDATE tblFoodMenus set
EventID = @EventId

Where FoodMenuID = IDENT_CURRENT(tblFoodMenus)

but it gives me this error:

Invalid column name tblFoodMenus

Am I using IDENT_CURRENT correctly?

P.S. FoodMenuID is the primary key of tblFoodMenus which is being auto incremented

Upvotes: 1

Views: 692

Answers (2)

ahains
ahains

Reputation: 1912

Mark Brackett's comment is right on the money - ident_current is not safe to get you the particular identity generated that you are interested in. Almost always you would want to use scope_identity() in the code that does the insert and then pass that around wherever it is needed.

Upvotes: 1

Mitch Wheat
Mitch Wheat

Reputation: 300728

Table name needs to be in quotes

Where FoodMenuID = IDENT_CURRENT('tblFoodMenus')

Upvotes: 4

Related Questions