Coldfusion 8 and Microsoft SQL 2005 : @@IDENTITY

There’s a handy little query you can run in SQL to retrieve the identity (primary key) value of a record you just created. So, you can do something like this:

This query will create a new person and also return the identity value of the newly created record. It can be useful for all kinds of things – I use it frequently.

We recently upgraded to ColdFusion 8 here at Purdue, and my code started breaking.

It turns out that CF8 attempts to “help” you by automatically running SELECT @@IDENTITY after any INSERT query. This is problematic in two ways – it cancels out your own @@IDENTITY query, and it arbitrarily names the result Generated_Keys.

So instead of being able to do something like this:

You must use this:

It’s really more annoying than anything else. It seems like a good idea to include the code automatically, but I think it would probably be a little better if there were a conditional in there that prevents CF8 from inserting its automatic query if a SELECT @@IDENTITY query is already present.

2 thoughts on “Coldfusion 8 and Microsoft SQL 2005 : @@IDENTITY

    1. Claire Post author

      I believe that in CF6, it didn’t select the identity value automatically. My SELECT @@IDENTITY syntax was used in CF6 and broke in CF8, because now CF does it for you.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *