Variable Placement in SQL Queries

When I’m creating complicating queries, I tend to build the query in SQL Query Analyzer before copying the code into my ColdFusion page. Other times I will copy the ColdFusion debugging code into Query Analyzer with the variable values to recreate a result set. Either way, handing variables can be tricky. I either have a hard time locating the variables to put in static values, or forget to remove static values and put back in the variable place holder before pasting back into CF. Anyone else do this?

To make my life easier, I’ve starting declaring all of my SQL variables at the top of the query. This has several benefits:

1. I can easily see all the variables and their data types at the top of the query
2. I don’t have to hunt around to find/replace values
3. I only need to pass in the same variable value once

Here’s an example of a query in Query Analyzer:

DECLARE @startDt DATETIME
, @endDt DATETIME

SET @startDt = '1/1/2004'
SET @endDt = '1/31/2004'

SELECT *
FROM SalesOrder SO
INNER JOIN SalesOrderDetail SOD ON SO.SalesOrderID = SOD.SalesOrderID
WHERE 1 = 1
AND (
@startDt IS NULL
OR SO.SalesOrderDt >= @startDt
)
AND (
@endDt IS NULL
OR SO.SalesOrderDt <= @endDt
)

For testing purposes, I’m getting all sales in January 2004 (Sorry, my test DB has OLD data). When I past this into CF, I only need to change two lines:

SET @startDt = <cfqueryparam cfsqltype="cf_sql_date" value="#startDt#"
null="#IIf(IsDate(startDt), DE("no"), DE("yes"))#" />
SET @endDt = <cfqueryparam cfsqltype="cf_sql_date" value="#startDt#"
null="#IIf(IsDate(startDt), DE("no"), DE("yes"))#" />

If either value is empty, a NULL value will be passed into the query and the WHERE clause will properly handle that value.

While it does create more code, it is a much more elegant – and stress free – way to design a query.