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