Wednesday, June 3, 2009

Optional parameters in SQL server

There might be situations where you would like to pass optional parameters to a stored procedure. Instead of constructing dynamic queries or doing many if else there is a cleaner way to do it.

SELECT [ClubId]
FROM [Club] where
(@ClubId IS NULL OR ClubId = @ClubId)

The where clause checks each parameter to see if it is NULL and in case it is checks the fields value with that of the parameter.