Monday, 28 February 2011

Optional Search Parameters in SQL Server Stored Procedures

I often create Stored Procedures in SQL Server for searching - but what is the best way to handle optional search parameters?

In the old days I wrote queries like this:

FROM Chimps
WHERE (@Age = 0 OR Age = @Age)
AND (@Weight = 0 OR Weight = @Weight)

... defaulting @Age and @Weight  to zero if not specified.

However, I later found that this performs a lot better:

FROM Chimps
WHERE Age = ISNULL(@Age, Age)
AND Weight = ISNULL(@Weight, Weight)

... with the params defaulting to NULL if not specified. Lovely.

But wait! What if there is a chance that some of our chimps have a NULL value for Age or Weight? Then we would have to go a bit further:

FROM Chimps
WHERE ISNULL(Age, 0) = COALESCE(@Age, Age, 0)
AND ISNULL(Weight, 0) = COALESCE(@Weight, Weight, 0)

This still performs well in most of my scenarios, but let's face it - ideally our chimps will not have a NULL weight. Weigh those chimps!

No comments:

Post a Comment