sql-server-2005 - 您将如何构建一个 Select 存储过程来处理来自表的任何请求？

Question

我想为 SQL 2005 构建一个单一的选择存储过程，该过程对于该表上的任何选择查询都是通用的。

**Columns**
LocationServiceID
LocationID
LocationServiceTypeID
ServiceName
ServiceCode
FlagActive

对于此表，我可能需要通过 LocationServiceID、LocationID、LocationServiceTypeID、ServiceName 或以上各项的组合进行选择。

我不想为它们中的每一个都设置一个单独的存储过程。

我假设最好的方法是在 NOT NULL 上构建“WHERE”语句。有点像

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)

这有意义吗？

Answer 1

这是我见过的关于该主题的最详尽的文章:

Dynamic Search Conditions in T-SQL by 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'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