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

我想为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)

那有意义吗？

最佳答案

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子句的那一行，否则它将返回与发送的值匹配的行。