SQL Server trick for matching passed parameter or all (IT related)

Hey...I found out something cool today in computing!

The problem: we want to allow a user to get data for either a specific parameter, or, if the user doesn't pass a parameter, then return all data. For example, we want to return employee data. If the user wants just one employee, they specify his/her employee number. If the user wants records for all employees, they don't specify any employee number.

The old solution: check if the parameter was passed, and if so run a specific query, if not run a general query. This looked something like -
 if parameter has a value
   run complex query
   for specific value
 else if no value (parameter is null)
   run complex query

This is clearly duplication as the line "run complex query" may be 10, 15, 20 lines long. The duplication was necessary because if the parameter was null, then we couldn't do an "equalk"-type exact match i.e. we didn't want to do any match at all in this case. For instance, employee number = 1 returns values, but employee number = NULL does not (if the employee number is null, then we would leave off the "employee number =" bit entirely to return all values). In SQL, NULL is not even a number. It's like the number you have when you don't have a number. No number can ever be "equal" to NULL (however we can test for NULL by saying "IF x IS NULL...")

The new solution: use a CASE statement in the WHERE clause, which means that theoretically the code would look like -
 run complex query
 if parameter has a value then for specific value only or else where the field matches itself

In practice this looks like -
 SELECT EmployeeNumber, EmployeeName, EmployeeOtherData
 FROM EmployeeTable
 WHERE EmployeeNumber = CASE WHEN @parameter IS NULL THEN EmployeeNumber ELSE @parameter END

In the above statement, when the passed parameter (@parameter) is null, we find records where the employee number from the table equals the employee number from the table (always true). If the passed parameter is not null, find records where the table's employee number matches the passed parameter.

This helped me heaps today. Made my code more readable, I didn't have to write as much code, and I had something to write about at lunchtime (and it was code-related). Yay!

0 Responses to "SQL Server trick for matching passed parameter or all (IT related)"

Post a Comment


Creative Commons LicenseThis work is licensed under a Creative Commons License. Copyright Thomas & Olivia Williams 2003, 2004, 2005, 2006, 2007, 2008, 2009