Home > View Post

Simpler Dynamic SQL Queries

I've been getting to grips with Reporting Services (again) recently and found myself browsing the bol tutorials. This one in particular: Tutorial: Using a Dynamic Query in a Report. It introduces dynamic queries with the following query:

Select c.firstname, c.lastname, e.title, d.departmentID
From HumanResources.EmployeeDepartmentHistory D INNER JOIN
HumanResources.Employee E ON D.EmployeeID = E.EmployeeID INNER JOIN
Person.Contact C ON E.ContactID = C.ContactID
Where D.DepartmentID = 1
Order By c.lastname

Fair enough. As the tutorial points out, it's best to work with a non-dynamic query at first so you don't have to enter all the column details manually. The next logical step in the tutorial is to replace the '1' above with a parameter. The tutorial doesn't mention that it's actually possible to do this with a very simple syntax:

Where D.DepartmentID = @Department

Yup, parameters can be used in the good old @fashioned way in the generic query designer. The reason the tutorial doesn't discuss this is because the solution I've shown above doesn't work properly. If you remember, the tutorial writers don't want a where clause if the @Department parameter is 0 - they want to return *all* records whereas my code above wouldn't return any (because there are no departments with a department id of 0). So they go with a query like the one shown below, which is the result of string concatenation and only inserts the where clause if the DepartmentID is not 0.

="SELECT c.firstname, c.lastname, e.title, d.departmentID " &
"From HumanResources.EmployeeDepartmentHistory D " &
"INNER JOIN HumanResources.Employee E " &
"ON D.EmployeeID = E.EmployeeID " &
"INNER JOIN Person.Contact C " &
"ON E.ContactID = C.ContactID " &
Iif(Parameters!Department.Value = 0, "", "WHERE D.DepartmentID = " & Parameters!Department.Value) &
"ORDER BY C.LastName"

Not exactly pretty but it works. However, there is a 'set-based' alternative using the simpler parameter style above. We can simply add an OR clause to the above query that effectively disables the WHERE clause if the @Department parameter is 0:

WHERE D.DepartmentID = @Department OR @Department = 0

Or, in full:

SELECT c.firstname, c.lastname, e.title, d.departmentID
FROM HumanResources.EmployeeDepartmentHistory D INNER JOIN
HumanResources.Employee E ON D.EmployeeID = E.EmployeeID INNER JOIN
Person.Contact C ON E.ContactID = C.ContactID
WHERE D.DepartmentID = @Department OR @Department = 0
ORDER BY c.lastname

Now that's better. And safer, if a developer was to accidentally open up the parameter to user input - the second approach would be much less vulnerable to a SQL injection attack.

This is a useful approach to bare in mind, particular inside stored procedures where you want the absence of a parameter to result in all records returned. Easy peasy.

Tags: SQL

 
Josh Post By Josh Twist
9:16 AM
09 Mar 2007

» Next Post: Volunteer comes forward with hack for VS on Vista
« Previous Post: Visual Studio 2005 Service Pack 1 Update for Windows Vista

Comments are closed for this post.

© 2005 - 2017 Josh Twist - All Rights Reserved.