views:

274

answers:

5

In a similar vein to my previous question I again ask the SO guys for your collective wisdom and help.

In a stored procedure and after passing some checks I need to insert a new row and return the newly created id for it. The check if a row exists works so it is the bit after that which I am undecided upon.

The table has two important columns: The LocationID and the CaseID. The CaseID is autoincrementing, so when you add insert a new locationid it will automatically rachet up.

I currently have this:

-- previous checks for existance of CaseID

IF @CaseID IS NULL
BEGIN
 INSERT INTO
  Cases(LocationID)
 VALUES
  (@LocationID)

 -- what now?
END

I was thinking of performing a @CaseID = (SELECT blah) statement immeadiately after but I was wondering if there is a better way?

Is there a better way? How would you do this?

+4  A: 
SELECT @CaseID = SCOPE_IDENTITY()

In fact, you can just do (if that's the end of the stored proc.):

SELECT SCOPE_IDENTITY()

(The OUTPUT clause is only available in SQL Server 2005 onwards...)

Ref: SCOPE_IDENTITY

Mitch Wheat
I don't think that would work because it seems as if he's already inserted the CaseID previously. He's now inserting a LocationID, hence the SCOPE_IDENTITY() would return the newly inserted LocationID, not the CaseID. I might be misunderstanding the question, though.
Joseph
you are. poster said that CaseId is the auto increment column...
Mitch Wheat
Ok I was trying to figure out why he was checking if @CaseID was NULL before doing the insert, that didn't make much sense to me
Joseph
I actually used SET @CaseID = @@IDENTITY but this is close enough to what I came up with eventually.
graham.reeds
+1  A: 

scope_identity()

KM
FYI, there is a known bug with Scope_Identity() and @@IDENTITY: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=328811
KM
I just came across the @@IDENTITY macro(?) and implemented it how Brandon Reno said most people would use it: SET @id = SCOPE_IDENTITY(), or in my case SET @id = @@IDENTITY
graham.reeds
Thanks for the tip, KM. I wasn't aware of that bug. Sounds like we're safe as long as we utilize the VALUES clause in the INSERT statement?
Rich.Carpenter
you could use the OUTPUT into a temp table to grab the results of an INSERT that uses a select too (sql server 2005 and up only)...
KM
A: 

You need to use the OUTPUT clause

http://blog.jemm.net/articles/databases/how-to-using-sql-server-2005s-output-to-return-generated-identity/

...which, as pointed out, is only available in sqlserver 2005. Plz disregard.

Visage
poster is using SQL Server 2000, which does not have the OUTPUT clause...
Mitch Wheat
Ah. My bad. I'll edit accordingly.
Visage
+1  A: 

SELECT SCOPE_IDENTITY()

IrishChieftain
+1  A: 

As others mentioned, SCOPE_IDENTITY() is the way to go, though some ORM tools provide this functionality as well.

The only thing you need to remember is SCOPE_IDENTITY() will return the last identity key value generated during the current session only. This is useful in filtering out new keys which may have been created by other clients simultaneously. SELECT @@IDENTITY will return the last key generated by any client/session.

Rich.Carpenter
Thanks Rich, I wasn't aware of that :-)
IrishChieftain