Jump to content
Marc

MSSQL Optional Parameters in a stored procedure

Recommended Posts

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 by Marc

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×