SQL Server - Execute first where clause and if valid don't execute second

Question

I have the SQL Server Select statement:

SELECT * 
FROM Table
WHERE(ClientPlants = 621 AND Carriers = 226)
 OR (Carriers = 226 AND ClientPlants IS NULL) 
 ORDER BY ClientPlants

Now problem is when the first is valid, it still executes the second clause after the 'or'. How do I make it so the second clause is only executed if the first clause fails?

As in, if there are no results found for where clause 1 (ClientPlants = 621 AND Carriers = 226), go to 2 (Carriers = 226 AND ClientPlants IS NULL). If there is a result for clause 1, return query and stop.

I tried to look into CASE statement but couldn't see how to add it to my code.

Thanks in advance!

Not sure I get your query. Why not WHERE ClientPlants = 621 OR Carriers = 226? — David P, Nov 22 '17 at 20:42
I guess the first question is what are you trying to achieve? from the query i see you want to get records where ClientPlants = 621 and also Carriers = 226 and also when Carriers = 226. The logic seems to ignore the ClientPlants filter — Teto, Nov 22 '17 at 20:48
Add some sample table data and the expected result. As formatted text, not images. — jarlh, Nov 22 '17 at 20:48
The answers below will do what you ask but more than likely a refined where clause will accomplished what you want. Posting sample data and desired results would help us help you. Or maybe an explanation. — DaniDev, Nov 22 '17 at 21:10

score 1 · Answer 1 · answered Nov 22 '17 at 20:41

1

You could use CASE WHEN:

SELECT * 
FROM Table
WHERE (CASE WHEN ClientPlants = 621 AND Carriers IN (226) THEN 1
            WHEN Carriers IN (226) THEN 1
            ELSE 0
       END) = 1
ORDER BY ClientPlantsK;

answered Nov 22 '17 at 20:41

Lukasz Szozda

162,964
23
234
275

Chris Mack · Accepted Answer · 2017-11-27T21:10:13.397

1

Do you mean something like this:

IF EXISTS (SELECT * FROM Table WHERE ClientPlants = 621 AND Carriers = 226)
    SELECT * FROM Table WHERE ClientPlants = 621 AND Carriers = 226 ORDER BY ClientPlantsK
ELSE
    SELECT * FROM Table WHERE Carriers = 226 ORDER BY ClientPlantsK
;

Update:

Given that you are only wanting to return 1 row, I think something like this would work for a single T-SQL query in a stored procedure:

CREATE PROCEDURE ProcedureName
    @ClientPlants int
    , @Carriers int
AS

SELECT
    Carriers
    , ClientPlants
    -- Add the rest of the columns
FROM
    (
        SELECT
            Carriers
            , ClientPlants
            -- Add the rest of the columns
            , ROW_NUMBER() OVER (ORDER BY CASE WHEN ClientPlants = @ClientPlants THEN 0 ELSE 1 END) R
        FROM Table
        WHERE Carriers = @Carriers
    ) Q
WHERE R = 1

Usage:

EXEC ProcedureName 621, 226

I've recommended you name your columns rather than using SELECT * to avoid having column R in the output.

edited Nov 27 '17 at 21:10

answered Nov 22 '17 at 20:42

Chris Mack

5,148
2
12
29

That's it. Didn't know you could put an if-else in there with SQL. Thanks so much Chris! – Scottie Nov 22 '17 at 20:57
You're welcome! - Please mark the answer as correct if it's working. – Chris Mack Nov 22 '17 at 21:02
1

Also, it's important to note that my answer is not just a single SQL query, but is technically procedural code. – Chris Mack Nov 22 '17 at 21:23
@Scottie. Pay attention to Chris comment above. While this may actually work there are probably many other ways to achieve what you are after that are way more efficient and elegant. SQL is not geared to operate procedurally and coding as such should be done as a last result. – DaniDev Nov 22 '17 at 22:37
Thanks DaniDev. Then how would I do via straight SQL? If I have to, I can put this into a stored proc for executing... – Scottie Nov 27 '17 at 18:59
@Scottie, if I understand your requirement, you need to alter your `WHERE` clause based on the existence of certain types of records within the table, (i.e. your `ClientPlants = 621 AND Carriers = 226` records - if any exist, you want only these, but if there aren't any, you want all records where `Carriers = 226`). It seems an unusual requirement, but if that's what it is, I don't see a way to do it in one T-SQL query. However, what I've posted does work, and will work fine in a stored procedure. – Chris Mack Nov 27 '17 at 19:03
Thanks Chris. Yes that is what I'm looking for. If a record has a specific carrier (226) with a specific plant (621), use that one (clause 1). Note, there will only be one record per plant/carrier pair. If it doesn't exist, then return the generic case for a specific plant (621) and the carrier is null (clause 2) – Scottie Nov 27 '17 at 19:29
@Scottie, given your last comment, I've updated my answer, as I think you might be able to do this in one T-SQL statement. – Chris Mack Nov 27 '17 at 20:06
I tried your answer but it doesn't work if I change the ClientPlant to be something other than 621 in the table (i.e. 620). I need it fall to the second clause of ClientPlant = 621 and Carrier IS NULL or not return anything if there is not a row with the carrier null in it. – Scottie Nov 27 '17 at 21:06
I've updated my answer - you could pass your values in via a stored procedure. – Chris Mack Nov 27 '17 at 21:11

score 0 · Answer 3 · edited Nov 22 '17 at 20:42

0

Do you mean like this?

SELECT a.*
       CASE WHEN ClientPlants = 621 AND Carriers = 226 THEN column
            WHEN ClientPlans <> 621 AND Carriers = 226 THEN column
         ELSE NULL
        END Column

 FROM table A

edited Nov 22 '17 at 20:42

Ousmane D.

54,915
8
91
126

answered Nov 22 '17 at 20:41

Isaiah3015

493
3
11

SQL Server - Execute first where clause and if valid don't execute second

3 Answers3