[sql] Sql Server return the value of identity column after insert statement

Is it possible in sql server using stored procedure to return the identity column value in a table against which some values are inserted? For example using stored procedure if we insert data in a table:

Table TBL

  • UserID integer, identity, auto-incremented
  • Name varchar
  • UserName varchar
  • Password varchar

So if I run the store procedure inserting some values like:

 Insert into TBL (Name, UserName, Password)
 Values ('example', 'example', '$2a$12$00WFrz3dOfLaIgpTYRJ9CeuB6VicjLGhLset8WtFrzvtpRekcP1lq')

How can I return the value of UserID at which this insertion will take place. I need The value of UserID for some other operations, can anybody solve this?

This question is related to sql sql-server-2008

The answer is


You can use SELECT @@IDENTITY as well


send an output parameter like

@newId int output

at the end use

    select @newId = Scope_Identity() 

     return @newId 

Here goes a bunch of different ways to get the ID, including Scope_Identity:

https://stackoverflow.com/a/42655/1504882


You can explicitly get back Identity columns using SqlServer's OUTPUT clause: Assuming you have an identity/auto-increment column called ID:

$sql='INSERT INTO [xyz].[myTable] (Field1, Field2, Field3) OUTPUT Inserted.ID VALUES  (1, 2, '3')';

Note that in Perl DBI you would then execute the INSERT statement, just like it was a SELECT. And capture the output like it was a SELECT

$sth->execute($sql);
@row=$sth->fetchrow_array; #Only 1 val in 1 row returned
print "Inserted ID: ", $row[0], "\n";

Presumably this is preferable because it doesn't require another request.


SELECT SCOPE_IDENTITY()

after the insert statement

Please refer the following links

http://msdn.microsoft.com/en-us/library/ms190315.aspx