Get the unique ID of a newly inserted row in MS SQL

[Scott]

I have a stored procedure in MS SQL, which simply inserts a new record in one of my tables. I want to get the unique ID of the newly inserted row. How can I do that?

I don’t have much db experience, so any help is appreciated.


Thanks,

Scott

[SMatanza]

Hey...
Try this:

<%
Set connInsert = Server.CreateObject("ADODB.Connection")
'Replace with your own connection string.
connInsert.Open "Driver={SQL Server};Server=(local);Database=YourDatabase;UID=workuser;PWD=workuser;"
varInsertCommand = "INSERT INTO tableName(TextField, NumericField) VALUES ('ABCDE', 12345);"
'Execute the insert command
connInsert.Execute(varInsertCommand)
'Create a recordset and SELECT the new Identity
Set rsNewID = connInsert.Execute("SELECT @@IDENTITY")
'Store the value of the new identity in variable varNewID
NewID = rsNewID(0)
rsNewID.Close
Set rsNewID = Nothing
%>

Good Luck !

[Scott]

Thank you very much!


Regards,

Scott

[raven]

This example given can actually be problematic on a very busy system. What you may want to consider is wrapping the calls in a transaction in order to be certain the identity you are returning is the identity in the scope of your current transaction.

Additionally you can avoid multiple calls by turning this into a single stored procedure returning the @@IDENTITY
_________________
Programmers do it in code