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>

<cfset dosomethingwith = myquery.newId />

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