How can I (both) create a row and accessing that row in the same 'Stored Procedure'?

Question

Hello,

I'd like to get the value of the id column for an object just after I've created it. But I don't want to run another query for that. My book for beginner (SQL Server 2008 for Dummies) says that there are 2 tables (inserted and deleted) that hold the last row(s) that have been inserted, updated, or deleted.

Unfortunately, only Triggers (says the book) can access those tables. But, if I use triggers, they will go off each time I "insert" a row even when I don't need them that functionality.

Can I obtain the same effect with a Store Procedure (without having to run a separate query?)

This is what I'm trying to do

CREATE PROCEDURE myProcedure
DECLARE @OrganizationName
        @ColumnID OUTPUT
AS
INSERT INTO Organization (OrganizationName)
VALUES (@OrganizationName)

SET @ColumnID = (// Please, I need Help here ...)

Thanks for helping

Answer 1

SELECT @@IDENTITY;

Answer 2

You can use SCOPE_IDENTITY():

SELECT @ColumnID = SCOPE_IDENTITY()

Answer 3

Use SCOPE_IDENTITY() function:

SELECT @ColumnID = SCOPE_IDENTITY()

Cheers Matthias

Answer 4

Just to clear up a misconception about triggers. You can create triggers "FOR UPDATE" which will cause them to fire only when an update occurs, and not for an insert.

But everyone else here is right, use SCOPE_IDENTITY().

The reason you would use SCOPE_IDENTITY() over @@IDENTITY is that @@IDENTITY is global to the server, so if you have triggers that create a record in another table with an IDENTITY field, the @@IDENTITY will hold that value.

SCOPE_IDENTITY() ensures that you get the ID from the table you just inserted to.

Answer 5

you can use the OUTPUT clause, which has access to INSERTED and DELETED (DELETED only on on update/delete):

DECLARE @NewRows table (YourIdentityColumn ....
                       ,any other columns here
                       )

INSERT INTO Organization
        (OrganizationName)
        OUTPUT INSERTED.YourIdentityColumn, any other columns here
            INTO @NewRows
    VALUES (@OrganizationName)

of course this is best when inserting multiple rows at a time:

INSERT INTO Organization
        (OrganizationName)
        OUTPUT INSERTED.YourIdentityColumn, any other columns here
            INTO @NewRows
    SELECT
        col1, col2, col3...
        FROM ...

you could also just return a result set (it will insert the row and return a result set to the procedure caller), from your INSERT:

INSERT INTO Organization
        (OrganizationName)
        OUTPUT INSERTED.YourIdentityColumn, any other columns here
    VALUES (@OrganizationName)