Retrieve inserted ID with Stored Procedure and @@identity

Here's a quick and dirty tut on how to retrieve the just inserted records ID with the use of Stored Procedures in SQL Server using the @@identity function

STEP 1: Returning the Identity of a inserted record with a Stored Procedure

Ok first of all, i am pretty new at this, but i thought this was very handy to share! (It's my first tutorial!!!)
Also i didný comment anything, i am just showing the way with a quick and dirty tutorial!

First of all you need to have a table:

Then create a stored procedure like this

That's all we have to do with our SQL-Server

STEP 2

Ok, switch to DM now and select Server Behaviors -> Command and make it look like this:

What you need to do is the following:

1. Provide a name for the command
2. Select your connections to the database
3. Select the Stored Procedure
4. Check return recordset and give it a name
5. Now select the Stored Procedure you just made
6. Give a size for the variable
7. Edit the runtime value to your whishes
8. Click OK!

Were almost done now, just one little step!

Comments

another way

November 15, 2002 by Bogos bogos

Hi there!

I Usually do in another way where i don't need to set the "SET NOCOUNT OFF".

Create Procedure TSI @test varchar(250),@outputid int

as insert into test(t1) values (@test)

set @id = @@identity

It always worked for me and you can drag the id into your layout and don't need to remove nothing from the code.

As i said, it always worked for me in all my web applications. :)

Does this replace the Insert Record Code

July 10, 2008 by Andrew Petrillo

This is very interesting, Will this function be used to replace the insert record function in DW, or is this something you can use along with the insert record function?

If it's used along with the insert record function, would you have to add in all of the fields that would be involved with the record insertion? for example, would I have to include firstname, lastname, fields if it's an info submission form? or will this basically plug into any form where you need to pull the new ID as it's inserted?

Sorry to sound soo green, im advanced in ASP, just never connected the dots in my head with SPs, I feel like im mentally missing a main ingredient so it's not clicking.

Thanks!

You must me logged in to write a comment.