47

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?

Improve this question
2

7 Answers 7

Reset to default
90 
Insert into TBL (Name, UserName, Password) Output Inserted.IdentityColumnName
 Values ('example', 'example', 'example')
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

@AmitSingh What is the difference between this and scope_identity? The sentence Inserted.IdentityColumnName always will return a correct identity?, What will happen if insert at the same time from two inserts?.
@GastonF. it will always give the right value it give output of any colunmn not only identity means it can return whole row which is inserted..u can try by typnig inserted.anycolumnname
@AmitSingh: great! +1, your post i helped me for find the differences in the use between this and scope_identity(), and I found something very crazy, the scope_identity() have a bug at least until version 2008, i do not know after that, anyway from Microsoft suggested use Output Inserted......wow!. Check this link. Thanks.
Note that the OUTPUT statement will return more than 1 row if your INSERT inserted more than 1 row, with a row containing each identity inserted, whereas SCOPE_IDENTITY() will return the last identity value inserted. So if you use SCOPE_IDENTITY() you better be sure that you can only ever insert 1 row. This is the case in your example.
Is it possible to store the ID in a variable when using OUTPUT?
16

send an output parameter like

@newId int output

at the end use

    select @newId = Scope_Identity() 

     return @newId
Improve this answer

Comments

9 
SELECT SCOPE_IDENTITY()

after the insert statement

Please refer the following links

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

Improve this answer

Comments

6

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.

Improve this answer

1 Comment

How is this any different than the accepted answer?
4

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

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

Improve this answer

Comments

1

You can use Scope_Identity() to get the last value.

Have a read of these too:

Improve this answer

1 Comment

@user2599269, I added a couple.
-1

You can use SELECT @@IDENTITY as well

Improve this answer

1 Comment

Please do not use that, as @@IDENTITY returns the last identity value use for ALL scopes, not just the current one, which can be wrong.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.