How would you build one Select stored procedure to handle any request from a table?

Question

I want to build a single select stored procedure for SQL 2005 that is universal for any select query on that table.

**Columns**
LocationServiceID
LocationID
LocationServiceTypeID
ServiceName
ServiceCode
FlagActive

For this table I may need to select by LocationServiceID, or LocationID, or LocationServiceTypeID or ServiceName or a combination of the above.

I'd rather not have a separate stored procedure for each of them.

I assume the best way to do it would be to build the 'WHERE' statement on NOT NULL. Something like

SELECT * FROM LocationServiceType WHERE

  IF @LocationID IS NOT NULL (LocationID = @LocationID)
  IF @LocationServiceID IS NOT NULL (LocationServiceID = @LocationServiceID)
  IF @LocationServiceTypeID IS NOT NULL (LocationServiceTypeID = @LocationServiceTypeID)
  IF @ServiceName IS NOT NULL (ServiceName = @ServiceName)
  IF @ServiceCode IS NOT NULL (ServiceCode = @ServiceCode)
  IF @FlagActive IS NOT NULL (FlagActive = @FlagActive)

Does that make sense?

Answer 1

here is the most extensive article I've ever seen on the subject:

Dynamic Search Conditions in T-SQL by Erland Sommarskog

here is an outline of the article:

 Introduction
      The Case Study: Searching Orders
      The Northgale Database
   Dynamic SQL
      Introduction
      Using sp_executesql
      Using the CLR
      Using EXEC()
      When Caching Is Not Really What You Want
   Static SQL
      Introduction
      x = @x OR @x IS NULL
      Using IF statements
      Umachandar's Bag of Tricks
      Using Temp Tables
      x = @x AND @x IS NOT NULL
      Handling Complex Conditions
   Hybrid Solutions – Using both Static and Dynamic SQL
      Using Views
      Using Inline Table Functions
   Conclusion
   Feedback and Acknowledgements
   Revision History

Answer 2

First of all, your code will not work. It should look like this:

SELECT * FROM LocationServiceType WHERE
(@LocationID IS NULL OR (LocationID = @LocationID)
... -- all other fields here

This is totally valid and known as 'all-in-one query'. But from a performance point of view this is not a perfect solution as soon as you don't allow SQL Server to select optimal plan. You can see more details here.

Bottom line: if your top priority is 'single SP', then use this approach. In case you care about the performance, look for a different solution.

Answer 3

What I've always done is is set the incoming parameters to null if should be ignored in query then check variable for null first, so if variable is null condition short circuits and filter is not applied. If variable has value then 'or' causes filter to be used. Has worked for me so far.

SET @LocationID = NULLIF(@LocationID, 0)
SET @LocationServiceID = NULLIF(@LocationServiceID, 0)
SET @LocationServiceTypeID = NULLIF(@LocationServiceTypeID, 0)


    SELECT * FROM LocationServiceType WHERE

      (@LocationID IS NULL OR LocationID = @LocationID)
     AND (@LocationServiceID IS NULL OR LocationServiceID = @LocationServiceID)
     AND (@LocationServiceTypeID IS NULL OR @LocationServiceTypeID = @LocationServiceTypeID)

etc...

Answer 4

SELECT * 
FROM LocationServiceType
WHERE LocationServiceID = ISNULL(@LocationServiceID,LocationServiceID)
  AND LocationID = ISNULL(@LocationID,LocationID)
  AND LocationServiceTypeID = ISNULL(@LocationServiceTypeID,LocationServiceTypeID)
  AND ServiceName = ISNULL(@ServiceName,ServiceName)
  AND ServiceCode = ISNULL(@ServiceCode,ServiceCode)
  AND FlagActive = ISNULL(@FlagActive,FlagActive)

If a null value is sent in it will cancel out that line of the where clause, otherwise it will return rows that match the value sent in.