MSSQL Optional Parameters in a stored procedure

[Marc]

Have had people ask me in the past what is the best way to go about optional parameters when using a stored procedure in sql server, so thought I would put up the way in which I do this

 

 

Example

 

CREATE PROC blahblah

@startdate DATETIME,
@enddate DATETIME,
@customerid INT

AS

SELECT *
FROM myOrders
WHERE CustomerID = @customerid
AND orderdate BETWEEN @startdate AND @enddate

 

Now lets say in the above we want to have customerid as optionally set. To do this we would need to give @customerid a default value. So we do this by adding "=NULL" to the end which will give it a default of null. However since it would not return any records this way due to the customer always having a value, we can achieve this by saying we want the record returned either if the customerid is same as we set or if NULL is the same as @customerID. Therefore setting null would actually return a record regardless of its value, as if we dont set it NULL will always equal @customerid (which would be NULL).

 

 

Example

 

CREATE PROC blahblah

@startdate DATETIME,
@enddate DATETIME,
@customerid INT = NULL

AS

SELECT *
FROM myOrders
WHERE (CustomerID = @customerid OR @customerid = NULL)
AND orderdate BETWEEN @startdate AND @enddate

Edited June 17, 2012 by Marc