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
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
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:
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
You can use Scope_Identity() to get the last value.
Have a read of these too:
Source: Stackoverflow.com