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.

Returning Delimited Lists in SQL

In a past CFUnited SQL presentation, I gave a demonstration on a Transact-SQL (SQL Server) concept known as cross apply which will return a delimited list of items in a query. This is a great way to roll up a one-to-many relationship into a single row. For instance, the following example returns all the reasons for which a sale was one, which may be zero or many. Regardless of the number of reasons, each sales order will only returned once, in one row:

SELECT TOP 100 SOH.SalesOrderID, SOH.OrderDate,
   LEFT(SalesReasonList, LEN(SalesReasonList) - 1) AS SalesReasonList
FROM Sales.SalesOrderHeader SOH CROSS APPLY (
   SELECT SR.Name + ', '
   FROM Sales.SalesOrderHeaderSalesReason SOHSR
      INNER JOIN Sales.SalesReason SR ON SOHSR.SalesReasonID = SR.SalesReasonID
   WHERE SOH.SalesOrderID = SOHSR.SalesOrderID
   ORDER BY SR.Name
   FOR XML PATH('')
) AS Cross1(SalesReasonList)
ORDER BY SOH.SalesOrderID DESC


You can download the full presentation on my presentation page.