我可以使用CASE语句来切换TSQLWHERE子句中的运算符吗

Question

我有一个复杂的存储过程，其中一部分生成动态SQL，以基于多种因素的各种方式透视数据。为了解决这个问题，我将举例说明一个简化的例子。

传入的参数之一是一个国家，然后将数据透视的数据过滤到该国家。然而，现在需求已经改变，可能会传入一个国家或一个大陆。那么，我想做的是创建一个WHERE子句，该子句使用CASE语句来使用=运算符或in子句，具体取决于传递的参数。

简化的数据库结构：

CREATE TABLE Countries
(Country VARCHAR(100),
Continent VARCHAR(20)
)
GO
INSERT INTO  Countries
VALUES ('UK','Europe'),('France','Europe'),('Portugal','Europe'),
('USA','North America'),('Canada','North America'),
('Brazil','South America'),('Peru','South America')
GO
CREATE TABLE Orders
(OrderID INT IDENTITY PRIMARY KEY,
OrderDate DATE,
ShipCity VARCHAR(100),
ShipCountry VARCHAR(100)
)
GO
INSERT INTO Orders (OrderDate, ShipCity, ShipCountry)
VALUES (GETDATE(), 'London', 'UK'),
(GETDATE(),'Paris','France'),
(GETDATE(),'Porto','Portugal'),
(GETDATE(),'Vale','USA'),
(GETDATE(),'Cusco','Peru'),
(GETDATE(),'Montreal','Canada'),
(GETDATE(),'Rio de Janeiro','Brazil')
GO

以下是我对存储过程的最新尝试(显然不起作用(：

CREATE PROC TestProc @Country VARCHAR(100)
AS
BEGIN
SELECT * 
FROM Orders o
WHERE 
CASE 
WHEN @Country='Europe' THEN o.ShipCountry IN (SELECT Country FROM Countries c WHERE c.Continent='Europe')
WHEN @Country='North America' THEN o.ShipCountry IN (SELECT Country FROM Countries c WHERE c.Continent='North America')
WHEN @Country='South America' THEN o.ShipCountry IN (SELECT Country FROM Countries c WHERE c.Continent='South America')
ELSE o.ShipCountry=@Country
END
END 

我也尝试过使用一个简单的案例而不是搜索案例，但也无法实现。

TBH，我认为最好的选择是有两个选项参数——@Country和@Continent，并重新编写匹配的调用过程。然而，在花了相当长的时间研究这件事之后，我想知道这是否真的可能，如果可能，如何实现。

谢谢。

Answer 1

DECLARE @Country VARCHAR(100) = 'France';
SELECT * 
FROM Orders o
WHERE 
o.ShipCountry IN (SELECT Country 
FROM Countries c 
WHERE c.Continent=CASE @Country WHEN '<Europe>' 
THEN 'Europe'
WHEN '<North America>'
THEN 'North America'
WHEN '<South America>'
THEN 'South America'
ELSE ''
END
or c.Country = @Country)

DBFIDDLE：https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=f15c9e79ff98789ec985556f76017985

编辑：甚至可以在没有CASE的情况下完成

DECLARE @Country VARCHAR(100) = '<Europe>';
SELECT o.* 
FROM Orders o
INNER JOIN Countries c ON c.Country = o.ShipCountry
WHERE o.ShipCountry = @Country
OR c.Continent = REPLACE(REPLACE(@Country,'<',''),'>','')

参见：DBFIDDLE(

Answer 2

您可以进行以下查询。

如果第一个查询没有结果，那么将测试第二个查询。

如果其中一个为真，则该行将被选择

DECLARE @country varchar;
SELECT * 
FROM Orders o
WHERE o.ShipCountry IN (SELECT Country FROM Countries c WHERE c.Continent=
CASE 
WHEN @Country='<Europe>' THEN 'Europe'
WHEN @Country='<North America>' THEN 'North America'
WHEN @Country='<South America>' THEN 'South America'
ELSE ''
END) OR o.ShipCountry = @Country

Answer 3

假设您使用澳大拉西亚或大洋洲作为大陆(而不是澳大利亚(，似乎您应该在此处使用参数化的IN(而不是用小于/大于(<>(的符号包装大陆名称(：

CREATE PROC dbo.TestProc @Country VARCHAR(100)
AS
BEGIN
SELECT {List of Explicit Columns you need goes here}
FROM dbo.Orders O
JOIN dbo.Countries C ON O.ShipCountry = C.Country
--If, for some reason, you don't have details of all relevant countries, use a LEFT JOIN
WHERE @Country IN (O.ShipCountry, C.Continent);
END 

db<gt；小提琴