Using @@IDENTITY or SCOPE_IDENTITY() + Insert, together in Stored Procedures

The code is like this

   INSERT INTO TABLE (VAL1,VAL2,VAL3) VALUES (X,Y,Z)

   GetLastInsertID @tablename='TABLE'

GetLastInsertID is this Stored Procedure:

   SELECT @@IDENTITY AS LastID FROM TABLE

How do I get the stored procedure to return the 'LastID' as requested in the Select @@IDENTITY statement above?

I get the following error:

   Incorrect syntax near 'GetLastInsertId'.

...but this works fine when executed by itself:

   GetLastInsertID @tablename='TABLE'

Okay, thanks i updated it to Scope_Identity(). But you're saying not to put it in a different SP, to put it in the same SP as the Insert?

Again, i still am getting an error when i combine an insert with this:

   SELECT SCOPE_IDENTITY() AS LastID FROM TABLE

Here is th开发者_JS百科e new error message:

   There is already an object named 'TABLE' in the database.

It's a bad idea to separate this into a stored procedure at all, because a stored procedure creates a new scope/context. That leaves you open to grabbing the wrong ID number. If one user in a session inserts many rows together, you might get the wrong result.

Instead, you almost always want the scope_identity() function, and you want to call it in the same context as the statement that created the new record.

In the first place you do not ever want to use @@identity as it can break if someone adds a trigger.

What you want to use is the OUTPUT clause or scope_identity. See Books online for examples of how to use OUTPUT.

your error is in your failure to include the EXECUTE command, try this:

INSERT INTO TABLE (VAL1,VAL2,VAL3) VALUES (X,Y,Z)

EXEC GetLastInsertID @tablename='TABLE'

the EXEC is assumed when you attempt to run a procedure with no other commands, however when you include the INSERT it makes the EXEC required.

Now, you really need to determine if what you are trying to do is a good design.

try this:

DECLARE @LastId int
INSERT INTO TABLE (VAL1,VAL2,VAL3) VALUES (X,Y,Z)
SELECT @LastID=SCOPE_IDENTITY()

Here is my sample code that does this. (But the stored proc doesn't add any value.)

--First create a test table.
    create table test
    (id int identity,
    name varchar(30))
    go

--A stored proc that returns the scope_identity()  
    create proc dbo.spTest
    as

    insert into test(name)
    values ('test')

    return scope_identity()

    go


-- Sample call
    declare @newId int

    exec @newId = spTest

    print @newId