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!
No comments:
Post a Comment