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.

Advertisements

Posted on September 11, 2011, in SQL, WebTricks. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: