Hi! How do I correctly create optional parameters for date ranges? Please see the Where clause in this example. SELECT QUOTE_LINES.QuoteID, QUOTE_LINES.LineNumber, QUOTE_LINES.LicenseCode, QUOTE.[Name], QUOTE.RFQ, QUOTE.QuoteDate, QUOTE.RFQDueDate, QUOTE_LINES.ThePartID, QUOTE_LINES.PartDesc, QUOTE_LINES.Qty, QUOTE_LINES.UnitPrice, QUOTE_LINES.TotalPrice, QUOTE_LINES.LeadTime, QUOTE_LINES.DateCompleted, QUOTE.SalesRep, QUOTE.Buyer, QUOTE.Phone, QUOTE.Fax, QUOTE.EMail, QUOTE.PLMnotes, QUOTE_LINES.Notes, QUOTE_LINES.Status, PartTBL.QTY_ON_HAND FROM QUOTE INNER JOIN QUOTE_LINES ON QUOTE.QuoteID = QUOTE_LINES.QuoteID INNER JOIN (SELECT ID, QTY_ON_HAND FROM TQDB.dbo.PART) AS PartTBL ON QUOTE_LINES.ThePartID =
PartTBL.ID WHERE (QUOTE.QuoteDate BETWEEN ISNULL(@StartDate, QuoteDate) AND ISNULL(@EndDate, QuoteDate)) or (QUOTE_LINES.TotalPrice BETWEEN ISNULL(@TotalPriceStart, TotalPrice) and ISNULL(@TotalPriceEnd, TotalPrice) or (QUOTE_LINES.LicenseCode ISNULL(@LicenseCode, LicenseCode) or (QUOTE_LINES.Status ISNULL(@Status, Status)
Just to make sure I understand, you want to be able to put this into a stored procedure and if the user does not provide a value then the where clause will not consider that paramater as a limitation? There are a couple of options. **Isnull** Giving a default value of null to your paramaters and using isnull as you have would work quite well, although that does mean that SQL Server will have to do evaluations for all of those entities even when it is pointless so it may not be the best from a performance standpoint. **Multiple Versions** Another alternative is to use a series of "if" statements to execute only the version of the query that has only the paramaters for which there is a value. The problem with this is that it would be extremely verbose and could be difficult to maintain since you would have multiple versions of what are essentially the same query. **Dynamic SQL** The alternative I generally use is to use dynamic SQL to only add on the parts of the where clause that are relavent. In doing this it is often convenient to start your where clause with somether like "where 1=1" that way you can have an "and" at the beginning of each of your possible additions without worrying about if it will be the first thing in the where clause. Using dynamic SQL has the advantage of not forcing the engine to consider irrelevant factors and being easier to maintain than numerous versions of the query. It also lets you print it out before executing which is handy for debugging. But it comes with all of the disadvantages of dynamic sql including risks of SQL injection (using sp_executesql with paramaters instead of just exec with the values populated will reduce, but not eliminate, this risk). **The choice** If performance is acceptable, I would stay with what you have. If performance is not acceptable, I would use dynamic sql, but with the understand of the risks that brings.