Using @@IDENTITY to Return the Value of an Identity Column

Let's discuss @@IDENTITY one more time. I thought you needed to use stored procedures (sps) to use @@IDENTITY to return the value of an identity column. That turns out to not be the case. Microsoft Support Online article Q195224 (http://support.microsoft.com/support/kb/articles/q195/2/24.asp) shows you two ways to return the value of an identity column using dynamic SQL and ActiveX Data Objects (ADO). The article notes that these techniques work with ADO versions 1.5 and 2.0. I tested these techniques using ADO 2.1.

The example in the article uses Visual Basic (VB) to demonstrate these techniques, but I constructed a simple Active Server Pages (ASP) sample to demonstrate and test the concepts. (See Listing 1). I've included the entire code listing so you can cut and paste it and try it yourself. My code is essentially the same as the code in the Support Online article except that I place the SQL in the sSQL variable for clarity. My code also uses the GetDSN function to return the DSN for the application.

The first technique uses the following SQL code:

SET NOCOUNT ON;INSERT idTest(Col1, Col2)  VALUES('" & strCol1 & "', '" & dtCol2 & "'); SELECT @@IDENTITY AS ID;SET NOCOUNT OFF

This is a compound SQL statement that inserts a record and returns the @@IDENTITY value. The first component of the statement sets the NOCOUNT option on, which is required to properly return the @@IDENTITY value. I used this SQL code in my sample code in Listing 1 and it works.

The article's second approach to returning the value of an identity column is to create an empty recordset, add a record to it, and then retrieve the @@IDENTITY value. The article uses the following code:

      sSQL = "SELECT * FROM idTest WHERE 1=0"      rsIdent.Open sSQL      rsIdent.AddNew      rsIdent.Fields("Col1").Value = strCol1      rsIdent.Fields("Col2").Value = dtCol2      rsIdent.Update

Response.write "
" & CStr(Now) & " rsIdent.id = " & rsIdent("id").Value

I could not get the above code to work with ADO 2.1, although I didn't play around with it much. This might be because of differences in how things work with different versions of ADO. So, you can use @@IDENTITY with dynamic SQL, but you might not be able to use it as shown in the article. Developers beware!

Listing 1

<%Function GetDSN()GetDSN = "dsn=MyCompany;uid=Customeruser;pwd=spot;database=MyCompany;"End FunctionDim rsIdent, sSQLDim strCol1Dim dtCol2       strCol1 = "Hello World!"      dtCol2 = Now      Set rsIdent = server.CreateObject("ADODB.Recordset")         rsIdent.ActiveConnection = getDSN()         rsIdent.CursorLocation = adUseServer         rsIdent.CursorType = adOpenKeyset         rsIdent.LockType = adLockOptimistic                  'Uncomment this line and it works without the Unique index.         sSQL = "SET NOCOUNT ON;INSERT idTest(Col1, Col2) " & _              "VALUES('" & strCol1 & "', '" & dtCol2 & "');" & _              "SELECT @@IDENTITY AS ID;SET NOCOUNT OFF"                 'Comment this line if you uncomment the one above.         ' sSQL = "SELECT * FROM idTest WHERE 1=0"                  rsIdent.Open sSQL      'Comment these next four lines if you use the Insert SQL statement.      Response.Write "
SQL Is : " & ssql              'rsIdent.AddNew      'rsIdent.Fields("Col1").Value = strCol1      'rsIdent.Fields("Col2").Value = dtCol2      'rsIdent.Update      Response.write "
" & CStr(Now) & " rsIdent.id = " & rsIdent("id").Value      Set rsIdent = Nothing%>