In an application I was building, I needed to get back the unique ID value generated by SQL Server after I inserted the record. The problem is that in a busy system, I might not get back the correct value. The solution was to use a stored procedure that returns two recordsets. Here's the SQL Server stored procedure I wrote:
CREATE Procedure sp_AddImpression
@AdID int,
@AdSpaceID int,
@Referer varchar(255),
@HostAddress varchar(40)
AS
INSERT INTO tblImpressions
(AdSpaceID, AdID, Referer, HostAddress)
VALUES
(@AdSpaceID, @AdID, @Referer, @HostAddress)
Select @@Identity as AdID
This inserts an ad impression record into a table, and then returns @@IDENTITY, which was the last identity value created. The interesting thing here is that the Insert statement also returns a recordset, even though it is empty. How do you get the second recordset? The often overlooked NextRecordset method. Here's the code in the ASP page:
Set rsImpression = Server.CreateObject("ADODB.Recordset")
rsImpression.Open "sp_AddImpression " & intAdID _
& ", " & intAdSpaceID _
& ", '" & Request.ServerVariables("HTTP_REFERER") _
& "', '" & Request.ServerVariables("REMOTE_HOST") & "'", dcnDB, adOpenStatic
set rsImpression = rsImpression.NextRecordset()
intAdID = rsImpression("AdID")
rsImpression.Close
I skip the first recordset generated by the Insert statement and go right to the second one, which has my identity value in it. This is helpful if you need the ID value back immediately and don't want to risk not getting the right ID back.