One reason occasionally offered for using dynamic SQL widely, or even for keeping business logic out of database stored procedures, is the difficulty of coding optional filters in SQL. Sometimes we may want a row from table X where column A = this, column B = this and column C = this. Other times we may want all the rows where column A = this but we’ll accept any value in columns B and C.

I’m not taking a position here on where the business logic belongs – but is it hard to code optional filters in SQL? No it sure isn’t. I’ll illustrate this in SQL Server 2008 Transact SQL as follows (NB: you may have to replace the quotation marks with “unsmart” single quotes to get the SQL to run):

SET NOCOUNT ON

CREATE TABLE T(A int NOT NULL, B int NOT NULL, C int NOT NULL, NullableD int NULL)

INSERT INTO T ( A ,B ,C, NullableD ) VALUES ( 1 ,1 ,1 , Null)
INSERT INTO T ( A ,B ,C,NullableD ) VALUES ( 2 ,2 ,2 ,2)

DECLARE @Filter1 int = 1
DECLARE @Filter2 int = 1
DECLARE @Filter3 int = 1
DECLARE @LoopIdx int = 0

WHILE @LoopIdx < 4
BEGIN

PRINT ‘@Filter1:’ + COALESCE(CAST(@Filter1 as nvarchar(1)), ‘Null’)
PRINT ‘@Filter2:’ + COALESCE(CAST(@Filter2 as nvarchar(1)), ‘Null’)
PRINT ‘@Filter3:’ + COALESCE(CAST(@Filter3 as nvarchar(1)), ‘Null’)

PRINT ‘(@LoopIdx:’ + CAST(@LoopIdx as nvarchar(1)) + ‘)’
/****THIS IS THE IMPORTANT BIT****/
SELECT * FROM T WHERE A = COALESCE(@Filter1, A) And B = COALESCE(@Filter2, B) And C=COALESCE(@Filter3, C)
/****END OF IMPORTANT BIT****/

SELECT @Filter3 = Null

IF @LoopIdx = 1
SELECT @Filter2 = Null

IF @LoopIdx = 2
SELECT @Filter1 = Null

SELECT @LoopIdx = @LoopIdx + 1

END

So any filter coded as COLUMN = COALESCE(@Filter,  COLUMN) will be optional. The stored procedure is defined with all the parameters that might be used.When calling it just pass in Null for any parameter which you don’t want to filter on.

I make no comment about how the optimizer will interpret this as it would require a lot of testing, but I started using this coding style in Oracle and SQL Server years ago and it hasn’t caused me any performance problems I’m aware of. I’m personally willing to sacrifice some performance for clarity and ease of maintenance – but if speed is the absolute priority it would no doubt be worth testing the Null and Not Null filters with different data distributions.

Another point to consider is what would happen with a nullable column. Assuming your database settings treat the result of [Null compared with Null] as Unknown, the logic won’t work. A null column value won’t be equal to itself – but if the column is non-numeric one possible approach is this:

SELECT * FROM T WHERE COALESCE(NullableD, ‘€£$UNLIKELYVALUE*&^’) = COALESCE(@Filter, ‘€£$UNLIKELYVALUE*&^’)

Note that wrapping the column in a function will prevent indexes being used but since a unique index can’t be created on a nullable column (although a UNIQUE constraint can) this may not make a lot of difference in practice. Of course, if your database treats Null as equal to Null this whole approach is not useful, but I’ve come across very few databases set up like that.

In the case of date or numeric columns it’s harder to be sure that the “unlikely” value will never occur, and actually I think the following SQL is clearer and therefore better in all cases:

WHERE (NullableD = @Filter Or @Filter Is Null)

NB: The brackets keep the “Or” from combining with any other expressions in the WHERE clause.

If you drop the following code into the same SQL Server window you can check how it works:

DECLARE @Filter int
SELECT NullableD FROM T
WHERE (NullableD =@Filter Or @Filter Is Null)

SELECT @Filter = 2
SELECT NullableD FROM T
WHERE (NullableD = @Filter Or @Filter Is Null)

So that’s all there is to it. There are some other filtering issues when using left outer joins. I hope to deal that topic in a future posting.