Marc Posted June 17, 2012 Share Posted June 17, 2012 (edited) 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 Nathan 1 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.