tsql - Return the id of the just added row

Question

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?

Answer 1

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