Sql Server return the value of identity column after insert statement

Question

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

User · Answer

send an output parameter like   newId int output   at the end use      select  newId   Scope Identity          return  newId

User · Answer

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- gt execute  sql    row  sth- gt 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

User · Answer

Insert into TBL  Name  UserName  Password  Output Inserted IdentityColumnName  Values   example    example    example

User · Answer

Here goes a bunch of different ways to get the ID  including Scope Identity   https   stackoverflow com a 42655 1504882

User · Answer

SELECT SCOPE IDENTITY     after the insert statement  Please refer the following links  http   msdn microsoft com en-us library ms190315 aspx

User · Answer

You can use Scope Identity   to get the last value   Have a read of these too    http   www mikesdotnetting com Article 54 Getting-the-identity-of-the-most-recently-added-record Stored procedure - return identity as output parameter or scalar

User · Answer

You can use SELECT   IDENTITY as well

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

Examples related to sql

Examples related to sql-server-2008