SQL

Showing posts with label stored procedures. Show all posts
Showing posts with label stored procedures. Show all posts

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:

SELECT *
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:

SELECT *
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:

SELECT *
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!