Sometimes 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