Dynamic WHERE conditions in Sql Server queries

During development you will often come across the need to write several stored procedures that each return the same columns, but where the number of records returned varies depending on the arguments supplied to the WHERE clause.

For example, say you had a document management system and you wanted the ability to return all documents in a given folder, as well as the need to return an individual document. This would normally require two seperate stored procedures:

    sp_GetDocumentsInFolder @folderId int

and

    sp_GetDocument @docId int

Whilst effective, this approach means that if the query changes in one stored procedure, you will also have to update the corresponding stored procedure to ensure that they both continue to return the same columns. The difficulty of this change is directly proportional to the complexity of the underlying query.

Another approach is to have one stored procedure that generates a dynamic SQL statement as a string variable that is passed to the sp_execute system stored procedure. This approach is even more cumbersome because it involves concatenating strings and you lose all the performance benefits of a stored procedures this way too, because the query plan cannot be cached by the database engine; it potentially leaves you open to SQL injection attacks also.

Wouldn't it be better if you could write the query once and only include conditions in the WHERE clause if necessary? Well, you can do just that using the following technique:

   DECLARE @docId int, @folderId int

   SET @docId = null
   SET @folderId = 100

   SELECT  ID,
           Name,
           FolderID
   FROM    Documents
   WHERE   (@docId IS NULL OR ID = @docId)
   AND     (@folderId IS NULL OR FolderID = @folderId)

In the example above, two variables are defined: one to hold the ID of the required document and the other to hold the ID of the folder whose documents are required. The WHERE condition includes logic to omit a condition if the corresponding variable is set to NULL, and in this example, the document ID is ignored because it is set to NULL and only documents in folder ID 100 will be returned.

To return a single document instead, we simply set the @docID variable to the primary key ID of the required document and set the @folderId to NULL, as follows:

    SET @docId = 5
    SET @folderId = null

You could of course set the @folderId value as well to only return the document if it existed in the specified folder.

Likewise, if you set both variables to NULL, all documents would be returned regardless of their ID or Folder ID values.

This query can be used in a stored procedure as follows:

    sp_GetDocumentDetails @folderID int, @docId int

Passing a value to the first argument and NULL to the second argument will result in all documents in that folder being returned. Passing NULL to the first argument and a value to the second argument results in only a single document being returned (if a matching ID was found of course). And finally, passing NULL to both arguments will return all documents.

Using this approach, the query to return document details only ever needs to be maintained in one place and the results from the stored procedure varies depending on the values passed in to the arguments. You also get all of the speed and security benefits of using stored procedures.

Copyright © 2009 Software Nation