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

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...