Searching with optional parameters
January 16th, 2009Sometimes we need to have optional parameters to a query. This is a very common scenario for searches, for instances. So how do you handle optional parameters? I’ve created a small script below where I query a table with two parameters, where I can use either one or two. By using ISNULL() I get a clean and nice way to set the parameters if necessary.
Please note query #1, where both parameters are null. Then all rows are returned.
CREATE TABLE #temp (n NVARCHAR(1), f NVARCHAR(1))
INSERT INTO #temp VALUES ('a', 'b')
INSERT INTO #temp VALUES ('a', 'c')
INSERT INTO #temp VALUES ('b', 'c')
INSERT INTO #temp VALUES ('e', 'd')
DECLARE @n AS NVARCHAR(1)
DECLARE @f AS NVARCHAR(1)
SELECT * FROM #temp WHERE n = ISNULL(@n, n) AND f = ISNULL(@f, f)
SET @n = 'a'
SELECT * FROM #temp WHERE n = ISNULL(@n, n) AND f = ISNULL(@f, f)
SET @f = 'c'
SELECT * FROM #temp WHERE n = ISNULL(@n, n) AND f = ISNULL(@f, f)
SET @n = NULL
SELECT * FROM #temp WHERE n = ISNULL(@n, n) AND f = ISNULL(@f, f)
DROP TABLE #temp
Results:
n f ---- ---- a b a c b c e d n f ---- ---- a b a c n f ---- ---- a c n f ---- ---- a c b c