Little cfquery gotcha
Here’s something I do quite regularly:
<cfquery name="myquery" datasource="dsn">
INSERT INTO foo VALUES ('bar')
SELECT SCOPE_IDENTITY() as newId
</cfquery>
This is all good until you do something like:
<cfquery name="myquery" datasource="dsn">
INSERT INTO foo SELECT 'bar'
SELECT SCOPE_IDENTITY() as newId
</cfquery>
<cfset dosomethingwith = myquery.newId />
For whatever reason, CF craps out here and complains that ‘myquery’ is not defined. The problem only occurs, as far as I am aware, when you use SELECT syntax in your INSERT statement, something that is often neccessary. The workaround is straightforward, thankfully:
<cfquery name="myquery" datasource="dsn">
DECLARE @newId int
INSERT INTO foo SELECT 'bar'
SET @newId = SCOPE_IDENTITY()
SELECT @newId as newId
</cfquery>
<cfset dosomethingwith = myquery.newId />
Thanks to Neil Smith for the work around; I’d been using a separate query with @@identity for some time until today - far from good.
Dom