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