Can I use a CASE statement to switch operators in a TSQL WHERE clause?-CodePudding

I have a complex stored proc, part of which generates dynamic SQL to pivot data in various ways based on a number of factors. For the purposes of this question, I'll illustrate a simplified example.

One of the parameters passed in is a country, and the data for the pivot is then filtered to that country. However, now requirements have changed, and either a country or a continent may be passed in. What I'd like to do, then, is to have a WHERE clause which uses a CASE statement to either use the = operator or an IN clause, depending on the parameter passed.

Simplified database structure:

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

Here's my latest attempt at the stored proc (which clearly doesn't work):

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

I've also tried using a simple case rather than a searched case, but couldn't get that to work either.

TBH, I'm thinking that the best option is to have two option parameters - @Country and @Continent, and to re-write the calling procedures to match. However, having spent quite a while working on this now, I'd like to know if it is, in fact, possible, and if so, how.

Thanks.

CodePudding user response：

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

EDIT: It's even possible to do it without 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,'<',''),'>','')

see: DBFIDDLE)

CodePudding user response：

you cor make following query.

If the first query doesn't have a result, the second will be tested.

If one is true the row will be selected

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

CodePudding user response：

Assuming you use Australasia or Oceania for the continent (and not Australia), seems you should just use a parametrised IN here (and not wrap continent names in less/greater than (<>) symbols):

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