Skip to content

Instantly share code, notes, and snippets.

@wellercs

wellercs/cf_cfqueryparamWithSQLVar

Created November 1, 2013 16:06
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save wellercs/7267631 to your computer and use it in GitHub Desktop.
Save wellercs/7267631 to your computer and use it in GitHub Desktop.
Download ZIP
Example of declaring and setting a SQL variable with cfqueryparam since there seems to be performance issues with large data sets when using cfqueryparam in a sub-select.
Raw
cf_cfqueryparamWithSQLVar
<cfset arguments.IsSold = 1>
<cfquery name="local.qryTraditional" datasource="cfartgallery">
SELECT
a1.ArtistID
, a1.FirstName
, a1.LastName
, (
SELECT TOP 1 LargeImage
FROM Art
WHERE IsSold = <cfqueryparam cfsqltype="cf_sql_bit" value="#arguments.IsSold#">
) AS FirstSoldLargeImage
FROM Artists a1
INNER JOIN Art a2
ON a1.ArtistID = a2.ArtistID
WHERE 1 = 1
</cfquery>
<cfquery name="local.qryWithSQLVar" datasource="cfartgallery">
DECLARE @IsSold AS BIT
@IsSold = <cfqueryparam cfsqltype="cf_sql_bit" value="#arguments.IsSold#">
SELECT
a1.ArtistID
, a1.FirstName
, a1.LastName
, (
SELECT TOP 1 LargeImage
FROM Art
WHERE IsSold = @IsSold
) AS FirstSoldLargeImage
FROM Artists a1
INNER JOIN Art a2
ON a1.ArtistID = a2.ArtistID
WHERE 1 = 1
</cfquery>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment