Use CASE or AND/OR in WHERE Clause To Limit Results

Question

I know there are lot of similar questions and I went through about 20 of them looking for an answer. Most set a single variable, or used the AND/OR format that I currently have.

I am trying to create a function where I pass in a combination of City, State, Zip and Country and it locates and returns all information. If I use the OR method the first option will return results if Zip and Country are NOT NULL, but then the option where only Zip is NOT NULL could also return a result. These are in the order I want them evaluated (top to bottom). I'm only retuning one value, but I can't guarantee that the results are in the same order, so I'd like to eliminate redundant results.

Here is my current query:

SELECT TOP(1) City, State_abbr, State_long, Zip, Country, County, Longitude, Latitude
      FROM [ULTRA].[dbo].[CityStateInfo]
      WHERE
        (   -- Zip Code, Country: Will find City and State  /* BEST RESULTS */
            @inZip IS NOT NULL AND 
            @inCountry IS NOT NULL 
            AND 
            Zip = @inZip AND 
            Country = @inCountry 
        )
        OR
        (   -- City, State, Country:  Will find first Zip Code in City, State
            @inCity IS NOT NULL AND 
            @inState IS NOT NULL AND 
            @inCountry IS NOT NULL 
            AND 
            City = @inCity AND 
            (State_abbr = @inState OR State_long = @inState) AND 
            Country = @inCountry 
        )
        OR
        (   -- City, State: Will find first Zip Code in City, State. Will find Country
            @inCity IS NOT NULL AND 
            @inState IS NOT NULL 
            AND 
            City = @inCity AND 
            (State_abbr = @inState OR State_long = @inState) 
        )
        OR          
        (   -- City, State, Zip: Will find Country
            @inState IS NOT NULL AND 
            @inCountry IS NOT NULL 
            AND 
            (State_abbr = @inState AND State_long = @inState) AND 
            Country = @inCountry 
        )
        OR
        (   -- City, Zip:  Will find State and Country
            @inCity IS NOT NULL AND 
            @inState IS NOT NULL 
            AND 
            City = @inCity AND 
            (State_abbr = @inState AND State_long = @inState) 
        )
        OR
        (   -- State, Zip:  Will find City and Country
            @inCity IS NOT NULL AND 
            @inCountry IS NOT NULL 
            AND 
            City = @inCity AND 
            Country = @inCountry 
        )
        OR
        (
            -- Zip Code: Will find City, State and Country  ** Possible error if Country is not US or Canada
            @inZip IS NOT NULL 
            AND 
            Zip = @inZip 
        )

This is what I tried with CASE

SELECT TOP(1) City, State_abbr, State_long, Zip, Country, County, Longitude, Latitude
      FROM [ULTRA].[dbo].[CityStateInfo]
      WHERE
        CASE
            -- Zip Code, Country: Will find City and State  /* BEST RESULTS */
            WHEN ( @inZip IS NOT NULL AND @inCountry IS NOT NULL )
                THEN Zip = @inZip AND Country = @inCountry 

            -- City, State, Country:  Will find first Zip Code in City, State
            WHEN ( @inCity IS NOT NULL AND @inState IS NOT NULL AND @inCountry IS NOT NULL )
                THEN City = @inCity AND (State_abbr = @inState OR State_long = @inState) AND Country = @inCountry 
        END

I received

Incorrect syntax near '='.

which corresponds to the = in "THEN Zip = @inZip AND Country = @inCountry.

If using the CASE isn't possible, is it possible to do something different with my OR statements to achieve what I'm looking for?

MS SQL Server v15.0.2070
SSMS v18.5

Alternative approach is to build query based in the values in the application(.net or else) and send prepared query with straightforward conditions to the sql server. Notice that `OR` queries have terrible performance. — Fabio, Jan 29 '21 at 00:00
@DaleK I like the idea of the Dynamic SQL. I tried it and I'm running into an odd error declaring my variables. — Dizzy49, Jan 29 '21 at 00:19
I really like the idea of the Dynamic Query, but I can't execute it in a function — Dizzy49, Jan 29 '21 at 02:02
You can use a `case` in a `where` clause, much like an `on` clause as shown in [this](https://stackoverflow.com/questions/10256848/can-i-use-case-statement-in-a-join-condition/10260297#10260297) answer. Performance is likely to suffer, though using `recompile` hint may help. — HABO, Jan 29 '21 at 03:48

score 1 · Accepted Answer · answered Jan 28 '21 at 23:51

To ensure the row is only returned once add additional logic to ensure its ignored if already found e.g. replace

(
    -- Zip Code: Will find City, State and Country  ** Possible error if Country is not US or Canada
    @inZip IS NOT NULL 
    AND Zip = @inZip 
)

with

(
    -- Zip Code: Will find City, State and Country  ** Possible error if Country is not US or Canada
    @inZip IS NOT NULL 
    -- Only go through this branch if country is null because otherwise we have handled it elsewhere
    AND @inCountry IS NULL 
    AND Zip = @inZip 
)

And use similar logic to handle other similar conditions.

This will work, but I will need to add more options since I am now being very specific around the variables. — Dizzy49, Jan 29 '21 at 02:03

score 1 · Answer 2 · answered Jan 29 '21 at 14:48

We can do a UNION ALL of each set of conditions, add an ordering column and take the first one.

This should be very performant, because it uses MERGE CONCATENATION, as Paul White describes:

SELECT TOP(1) City, State_abbr, State_long, Zip, Country, County, Longitude, Latitude
    (SELECT TOP(1) 1 AS Ordering, City, State_abbr, State_long, Zip, Country, County, Longitude, Latitude
      FROM [ULTRA].[dbo].[CityStateInfo]
      WHERE
        (   -- Zip Code, Country: Will find City and State  /* BEST RESULTS */
            @inZip IS NOT NULL AND 
            @inCountry IS NOT NULL 
            AND 
            Zip = @inZip AND 
            Country = @inCountry 
        )
    UNION ALL
    SELECT TOP(1) 2, City, State_abbr, State_long, Zip, Country, County, Longitude, Latitude
      FROM [ULTRA].[dbo].[CityStateInfo]
      WHERE
        (   -- City, State, Country:  Will find first Zip Code in City, State
            @inCity IS NOT NULL AND 
            @inState IS NOT NULL AND 
            @inCountry IS NOT NULL 
            AND 
            City = @inCity AND 
            (State_abbr = @inState OR State_long = @inState) AND 
            Country = @inCountry 
        )
    UNION ALL
    SELECT TOP(1) 3, City, State_abbr, State_long, Zip, Country, County, Longitude, Latitude
      FROM [ULTRA].[dbo].[CityStateInfo]
      WHERE
        (   -- City, State: Will find first Zip Code in City, State. Will find Country
            @inCity IS NOT NULL AND 
            @inState IS NOT NULL 
            AND 
            City = @inCity AND 
            (State_abbr = @inState OR State_long = @inState) 
        )
    UNION ALL
    SELECT TOP(1) 4, City, State_abbr, State_long, Zip, Country, County, Longitude, Latitude
      FROM [ULTRA].[dbo].[CityStateInfo]
      WHERE
        (   -- City, State, Zip: Will find Country
            @inState IS NOT NULL AND 
            @inCountry IS NOT NULL 
            AND 
            (State_abbr = @inState AND State_long = @inState) AND 
            Country = @inCountry 
        )
    UNION ALL
    SELECT TOP(1) 5, City, State_abbr, State_long, Zip, Country, County, Longitude, Latitude
      FROM [ULTRA].[dbo].[CityStateInfo]
      WHERE
        (   -- City, Zip:  Will find State and Country
            @inCity IS NOT NULL AND 
            @inState IS NOT NULL 
            AND 
            City = @inCity AND 
            (State_abbr = @inState AND State_long = @inState) 
        )
    UNION ALL
    SELECT TOP(1) 6, City, State_abbr, State_long, Zip, Country, County, Longitude, Latitude
      FROM [ULTRA].[dbo].[CityStateInfo]
      WHERE
        (   -- State, Zip:  Will find City and Country
            @inCity IS NOT NULL AND 
            @inCountry IS NOT NULL 
            AND 
            City = @inCity AND 
            Country = @inCountry 
        )
    UNION ALL
    SELECT TOP(1) 7, City, State_abbr, State_long, Zip, Country, County, Longitude, Latitude
      FROM [ULTRA].[dbo].[CityStateInfo]
      WHERE
        (
            -- Zip Code: Will find City, State and Country  ** Possible error if Country is not US or Canada
            @inZip IS NOT NULL 
            AND 
            Zip = @inZip 
        )
) t
ORDER BY ordering;

Moe Sisko · Answer 3 · 2021-01-29T06:05:08.363

Based on your CASE example, I'm guessing you are after something like this (?):

SELECT TOP(1) City, State_abbr, State_long, Zip, Country, County, Longitude, Latitude
      FROM [ULTRA].[dbo].[CityStateInfo]
      WHERE
        1 = CASE
                -- Zip Code, Country: Will find City and State  /* BEST RESULTS */
                WHEN ( @inZip IS NOT NULL AND @inCountry IS NOT NULL )
                    THEN 
                        CASE WHEN Zip = @inZip AND Country = @inCountry THEN 1 ELSE 0 END

                -- City, State, Country:  Will find first Zip Code in City, State
                WHEN ( @inCity IS NOT NULL AND @inState IS NOT NULL AND @inCountry IS NOT NULL )
                    THEN 
                        CASE WHEN City = @inCity AND (State_abbr = @inState OR State_long = @inState) AND Country = @inCountry 
                            THEN 1 ELSE 0 END
                ELSE 0          
            END

Note that the "ELSE 0" parts are optional, and can be removed if you want, since if a CASE doesn't match any condition and falls through to the end, it will return NULL, which will never equal 1.

The problem with this approach is that you almost certainly lose sargability. — Dale K, Jan 29 '21 at 06:06

Use CASE or AND/OR in WHERE Clause To Limit Results

3 Answers3