How can I retrieve a SQL variable from cfquery?

Question

I am running this inside of my cfquery.

SET @rID = ( SELECT TOP 1 roleid
             FROM Roles
             WHERE RoleName = @rName AND appid = @appID
             ORDER BY Created DESC);

Is it possible to retrieve @rID without having to run the SELECT query a second time? As in:

<cfset varName = queryName.rID>

The above doesn't work obviously, but is there any other way to return the variable from the query?

Answer 1

You can get the value of @rID by selecting it without the need to run the full query again.

<cfquery name="qryRoleID">
    SET @rID = ( SELECT TOP 1 roleid
         FROM Roles
         WHERE RoleName = @rName AND appid = @appID
         ORDER BY Created DESC);
    SELECT @rID AS rID
</cfquery>
<cfdump var="#qryRoleID.rID#">