Sql: If Clause Within Where Clause

SQL: IF clause within WHERE clause

Use a CASE statement

UPDATE: The previous syntax (as pointed out by a few people) doesn't work. You can use CASE as follows:

WHERE OrderNumber LIKE
  CASE WHEN IsNumeric(@OrderNumber) = 1 THEN 
    @OrderNumber 
  ELSE
    '%' + @OrderNumber
  END

Or you can use an IF statement like @N. J. Reed points out.

SQL: IF/CASE statement within WHERE clause

We can express your logic using a union:

select * from #Fruits where Fruit in ('Apple', 'Grapes')
union all
select * from #Fruits where Color = 'Green' and
                            not exists (select 1 from #Fruits
                                        where Fruit in ('Apple', 'Grapes'));

We might also be able to combine the logic into a single query:

select *
from #Fruits
where
    Fruit in ('Apple', 'Grapes') or
    (Color = 'Green' and
     not exists (select 1 from #Fruits where Fruit in ('Apple', 'Grapes'));

in operator inside if condition and where clause in sql server

Just use regular boolean expressions:

where (@input = 'web' and col6 in (18, 19, 20, 22)) or
      (@input = 'net' and col6 in (37, 69, 26, 25)) or
      (@input = 'com' and col6 in (55, 66, 46, 27)) or
      (@input not in ('web', 'net', 'com') and col6 in (55, 46, 46, 17))

SQL Server : IF condition within where clause

That's not how the CASE statement works. I think this does what you want:

where  g.ReportInstanceID = blah
    and rcr.FormID = blah
    and rcr.FormSectionID = blah
    and rcr.SubSectionID = blah
    and (
          (rcr.DataCollectionPeriodID = 163 and PDFType IS NOT NULL)
          or rcr.DataCollectionPeriodID <> 163
        )

if else condition in where clause in ms sql server

I think we can simply do this in short because in both the cases a.starttime >= b.starttime is applicable so we just need to handle a.endtime <= b.endtime condition in case of b.enddate is NULL or NOT NULL so I do as below:

WHERE a.starttime >= b.starttime and (b.enddate is null OR a.endtime <= b.endtime)

Many conditions depends on IF clause within WHERE clause

As you've seen you can't use an if like that, but you can create the desired behavior using the and and or logical operators:

SELECT *
FROM   table
WHERE  (@value IS NOT NULL AND 
        id > 10 AND 
        name = 'example' AND 
        address IS NOT NULL AND -- etc...) OR
       (@value IS NULL AND email IS NOT NULL)

how can put if condition in where clause

SELECT *
FROM LocationOutsw W
WHERE ( @p_type = 'ALL'
    AND ( W.NAME LIKE '%'+ @p_search + '%'
            OR W.DESCRIPTION LIKE '%'+ @p_search + '%'
        )       
) 
OR (    @p_type = 'NAME'
    AND W.NAME LIKE '%'+ @p_search + '%'
)
OR ( @p_type = 'DESCRIPTION'
    AND W.DESCRIPTION LIKE '%'+ @p_search + '%'
)

But I strongly recommend not to write your queries like so, because of wrong query plans you will get.

If Statement in my where clause with dates

Since you said you can use DATEPART, the case statement checks if the current day is Monday, if it is, it subtracts 3 days to get Friday, else it subtracts 1 day, DATEPART returns a numeric value starting with 1 for Sunday and moves to 7 for Saturday, so as Monday is the date of concern we check for the value of 2, this is SQL Server syntax.

select p.businessDate, p.accountNumber (more fields, but unecessary)
from Table p, 
where businessDate = CASE WHEN DATEPART(WEEKDAY, GETDATE()) = 2 
THEN DATEADD(DAY, -3, GETDATE()) 
ELSE DATEADD(DAY, -1, GETDATE()) END

DATEPART documentation - datepart-transact-sql

---

Related Topics