Marc Posted June 17, 2012 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
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.