Clever Geek Handbook

SQL Server: if else On AND Clause

I am using SQL Server and I am stuck with a condition where I need to run clause 1 AND

or 2 AND

based on a condition if

.

See below request, I tried to explain my requirement. I want to execute the query below, but in the sentence where

I have 2 conditions AND

, resulting in a need to run based on a variable @toRun

like this:

  • If @toRun

    equal to "first", then condition 1 AND

    must be met
  • Else 2nd AND

    will work.

Can anyone help me with this, I've tried both with if

and case

but can't find a way to do it.

declare @toRun nvarchar(10) = 'first';
select * 
From tbl_data
where isDeleted=0

--if @toRun is equal to 'first' then following clause should run
--1st Clause:
AND FID in (select FID from t1)

--if toRun is not equal to 'first' then following clause should run
--2nd Clause:
AND TID in (select TID from t1)
+3


source to share


2 answers


Try this: adding both conditions, but using an OR clause, and some extra parentheses can help you run one or the other.

declare @toRun nvarchar(10) = 'first';
select * 
From tbl_data
where 
    isDeleted=0 AND
      (
        (@toRun = 'first' AND FID IN (SELECT FID FROM T1)) OR 
        (@ToRun != 'first' AND TID IN (SELECT TID FROM T1))
      )


If in the future you really need to use a statement IF

to do something (in which case, I really don't think it is necessary), your syntax would be:

declare @toRun nvarchar(10) = 'first';


IF @toRun = 'first'
  BEGIN 
    select * 
    From tbl_data
    where 
        isDeleted=0 AND
        FID IN (SELECT FID FROM T1)
  END

IF @toRun != 'first'
  BEGIN 
    select * 
    From tbl_data
    where 
        isDeleted=0 AND
        TID IN (SELECT TID FROM T1)
  END
+2


source


This is another way to do it using EXISTS 



DECLARE @ToRun nvarchar(10) = 'first';

SELECT *  
FROM tbl_data t
WHERE isDeleted = 0 AND 
      EXISTS (SELECT 1 FROM t1 
              WHERE (@ToRun = 'first' AND t.FID = t1.FID) OR 
                    (@ToRun <> 'first' AND t.TID = t1.TID)
             )
+3


source






More articles:


All Articles