Table search / filter template

Searching data within SQL can use many approaches. Often when writing applications, I would find multiple stored procedures to query the same table using different sets of parameters/conditions. This seemed silly to me, since the table schema never changed.

To eliminate such redundant stored procs, I came up with the following query. It's pretty easy to template according on your needs.

The basis of the idea is to start with a single stored procedure which provides all filter options as parameters. Within the sproc, the table filters based on ONLY the parameters that have been specified; any parameters not supplied are given a default value of NULL, and are ignored by the SELECT query.

This sample also includes additional options for the datetime field, to support specific values as well as date ranges (though in this example, use of the date range does require both min AND max).
Language:
T-SQL
Keywords:
Code Snippet

CREATE PROCEDURE tblXYZ_Search
(
  @intField int = NULL
, @dateField datetime = NULL
, @dateFieldMin datetime = NULL
, @dateFieldMax datetime = NULL
)
AS
 
SELECT *
  FROM [tblXYZ]
 WHERE (@intField IS NULL OR @intField = [tblXYZ].[intField])
   AND (@dateField IS NULL OR @dateField = [tblXYZ].[dateField])
   AND (    (@dateFieldMin IS NULL OR @dateFieldMax IS NULL)
         OR [tblXYZ].[dateField] BETWEEN @dateFieldMin AND @dateFieldMax)
   AND ...


Created 2012-02-11
comments powered by Disqus
Login