How would you build one Select a stored procedure to process any query from a table?

Question

I want to create a single selective stored procedure for SQL 2005 that is universal for any select query in this table.

**Columns**
LocationServiceID
LocationID
LocationServiceTypeID
ServiceName
ServiceCode
FlagActive

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

I would prefer not to have a separate stored procedure for each of them.

I guess the best way to do this is to construct the WHERE statement on NOT NULL. Sort of

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)

It makes sense?

Answer 1

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)

, where, , .

Answer 2

, - :

T-SQL Erland Sommarskog

:

 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 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 3

, . :

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

"--". , SQL Server . .

: - " SP", . , .

Answer 4

What I always did was set the input parameters to zero, if they should be ignored in the request, then first check the variable for null, so if the variable has zero conditions, then short circuits and a filter are not applied. If the variable matters, then "or" causes the filter to be used. I have worked 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...