T-SQL Conditional Where Clause

T-SQL Conditional WHERE Clause

I changed the query to use EXISTS because if there's more than one location associated with a POST, there'd be duplicate POST records that'd require a DISTINCT or GROUP BY clause to get rid of...

The non-sargable

This will perform the worst of the possible solutions:

SELECT p.*
  FROM POSTS p
 WHERE EXISTS(SELECT NULL
                FROM LOCATIONS l
               WHERE l.LocationId = p.LocationId
                 AND l.Condition1 = @Value1
                 AND l.SomeOtherCondition = @SomeOtherValue)
   AND (@IncludeBelow = 1 OR p.LocationTypeId = @LocationType)

The sargable, non-dynamic version

Self explanitory....

BEGIN
  IF @IncludeBelow = 0 THEN
    SELECT p.*
      FROM POSTS p
     WHERE EXISTS(SELECT NULL
                    FROM LOCATIONS l
                   WHERE l.LocationId = p.LocationId
                     AND l.Condition1 = @Value1
                     AND l.SomeOtherCondition = @SomeOtherValue)
       AND p.LocationTypeId = @LocationType
  ELSE
    SELECT p.*
      FROM POSTS p
     WHERE EXISTS(SELECT NULL
                    FROM LOCATIONS l
                   WHERE l.LocationId = p.LocationId
                     AND l.Condition1 = @Value1
                     AND l.SomeOtherCondition = @SomeOtherValue) 
END

The sargable, dynamic version (SQL Server 2005+):

Love or hate it, dynamic SQL lets you write the query once. Just be aware that sp_executesql caches the query plan, unlike EXEC in SQL Server. Highly recommend reading The Curse and Blessings of Dynamic SQL before considering dynamic SQL on SQL Server...

DECLARE @SQL VARCHAR(MAX)
    SET @SQL = 'SELECT p.*
                  FROM POSTS p
                 WHERE EXISTS(SELECT NULL
                                FROM LOCATIONS l
                               WHERE l.LocationId = p.LocationId
                                 AND l.Condition1 = @Value1
                                 AND l.SomeOtherCondition = @SomeOtherValue)'

    SET @SQL = @SQL + CASE 
                        WHEN @IncludeBelow = 0 THEN
                         ' AND p.LocationTypeId = @LocationType '
                        ELSE ''
                      END   

BEGIN 

  EXEC sp_executesql @SQL, 
                     N'@Value1 INT, @SomeOtherValue VARCHAR(40), @LocationType INT',
                     @Value1, @SomeOtherValue, @LocationType

END

Conditional WHERE clause in SQL Server

Try this

SELECT 
    DateAppr,
    TimeAppr,
    TAT,
    LaserLTR,
    Permit,
    LtrPrinter,
    JobName,
    JobNumber,
    JobDesc,
    ActQty,
    (ActQty-LtrPrinted) AS L,
    (ActQty-QtyInserted) AS M,
    ((ActQty-LtrPrinted)-(ActQty-QtyInserted)) AS N
FROM 
    [test].[dbo].[MM]
WHERE 
    DateDropped = 0
    AND (
    (ISNULL(@JobsOnHold, 0) = 1 AND DateAppr >= 0) 
    OR 
    (ISNULL(@JobsOnHold, 0) != 1 AND DateAppr != 0)
    )

You can read more about conditional WHERE here.

SQL Server conditional where clause based on declared variable

You are looking for this

DECLARE @ITEST INT = 1

SELECT NAME, LNAME, CADDRESS
FROM JEEVEN
WHERE (@ITEST = 1 AND EVEN_KEY > 5 AND EVEN_KEY < 10)
   OR (@ITEST = 2 AND EVEN_KEY > 20 AND EVEN_KEY < 30)

T-SQL - Conditional WHERE clause

Sometime you modify table/function in such a manner that it don't affect any part of code.you can do similar thing here.

Or even alternate solution will be neat clean .In case you have lot million of records and your real scenario is different than one describe above then you can think of dynamic search,but the one you have written is not upto the mark.

    drop type [dbo].[Param]
    CREATE TYPE [dbo].[Param] AS TABLE ( [FlagVal] NVARCHAR(100),FlagID as 
    case when FlagVal='Yes' then 10 when FlagVal='No' then 100 
    when FlagVal like '%Maybe%' then 1000 else null end)
    GO

    DECLARE @Param [dbo].[Param];
    INSERT  INTO @Param ( [FlagVal] )
    VALUES    ( 'Yes' )
            , ( 'NO' )
            , ( 'Maybe_JKL' )
            , ( 'Maybe_XYZ' )
            , ( 'Maybe_PQR' )
            ;

    DECLARE @DataT TABLE ( [ID] INT IDENTITY(1,1), [Flag] INT, [FValY] NVARCHAR(45), 
    [FValN] NVARCHAR(45), [FValMB] NVARCHAR(45) );
    INSERT  INTO @DataT ( [Flag], [FValY], [FValN], [FValMB] )
    VALUES    ( 10, 'XYZ', NULL, NULL )
            , ( 10, 'ABC', NULL, NULL )
            , ( 10, 'XYZ', NULL, NULL )
            , ( 100, NULL, 'MNO', NULL )
            , ( 100, NULL, 'STU', NULL )
            , ( 1000, NULL, NULL, 'Maybe_JKL' )
            , ( 1000, NULL, NULL, 'Maybe_XYZ' )
            , ( 1000, NULL, NULL, 'Maybe_PQR' )
            , ( NULL, NULL, NULL, NULL )
            , ( NULL, NULL, NULL, NULL );

    SELECT * from @DataT
    select * from @Param

    select * from @DataT dt
where exists(select [FlagVal] from @Param p 
where p.FlagID=dt.[Flag] and(( p.FlagVal=dt.FValMB) or (dt.FValMB is null)))

    ------Alternatively declare table variable and use it instead of table type
    DECLARE @TableTypeAlt TABLE ( [FlagVal]  NVARCHAR(100),FlagID int)
    insert into @TableTypeAlt
    select FlagVal, 
    case when FlagVal='Yes' then 10 
    when FlagVal='No' then 100 when FlagVal like '%Maybe%' then 1000 else null end
    from @Param
    select * from @TableTypeAlt

-- Alternatively 3rd solution,if you cannot change type at all
then try this,

select * from @DataT dt
where exists(
select [FlagVal] from @Param p 
where case when FlagVal='Yes' then 10 when FlagVal='No' then 100 
    when FlagVal like '%Maybe%' then 1000 else null end=dt.[Flag] and(( p.FlagVal=dt.FValMB) or (dt.FValMB is null))
)

Conditional 'Case When' within 'Where' Clause T-SQL

You can't use a case statement like that (evaluating different boolean expressions based on input), but you can rewrite your logic with boolean AND and OR instead:

where 
    ([days same form] <= 9 and 
     datediff(month, leasedate ,'2016-08-01 00:00:00') >= 8)
    or 
    ([days same form] > 9 and 
     datediff(month, rentlastchanged ,'2016-08-01 00:00:00') >= 12))

Conditional WHERE Clauses In A Stored Procedure

As suggested in the comments, the best way to handle this kind of conditional where clause would be to use dynamic-sql ..... Something like....

CREATE PROCEDURE myProc
    @homeID INT,
    @name VARCHAR(500),
    @hometype_enum myIntArray READONLY,
    @country_enum myIntArray READONLY
AS
BEGIN
 SET NOCOUNT ON

 Declare @Sql NVarchar(MAX);

 SET @Sql = N' SELECT * FROM my_table '
          + N' WHERE name = @name '
           + CASE WHEN EXISTS (Select * FROM @hometype_enum)
             THEN N' AND hometype IN (SELECT val FROM hometype_enum) ' ELSE N' ' END
           + CASE WHEN EXISTS (Select * FROM @country_enum)
             THEN N' AND country IN (SELECT val FROM country_enum ) ' ELSE N' ' END

  Exec sp_executesql @Sql
                    ,N'@homeID INT , @name VARCHAR(500),
                      @hometype_enum myIntArray, @country_enum myIntArray'
                    ,@homeID
                    ,@name
                    ,@hometype_enum
                    ,@country_enum

END
GO

Using sp_executesql will allow sql server to store parameterised execution plans for the same stored procedure. It is different execution plans for different sets/combinations of a parameters for the same stored procedure for optimal performance.

Conditional Where clause in SQL query if one or both date range parameters are null

Don't cast your column, it'll be bad for performance. I also assume that your parameters are date data types, but that your column is actually a date and time (such as a datetime2), due to your casting. You can then just use a single ISNULL on the parameters, and then an OR for when they are both NULL:

WHERE (CreateDate >= @StartDate AND CreateDate < DATEADD(DAY,1,ISNULL(@EndDate,@StartDate))
   OR (@StartDate IS NULL AND @EndDate IS NULL)

You'll probably want to put an OPTION (RECOMPILE) in there too, as the plan for when they both have NULL could be very different to when they don't.

T-SQL Conditional WHERE clause based on result of WHERE clause

I'm guessing something like this is what you are after

SELECT    1.A,
          COALESCE(2.B, 3.B) AS B,
          CASE 
           WHEN 2.B IS NULL THEN 3.C 
           ELSE 2.C 
          END AS C
FROM      [Table] 1
LEFT JOIN [Table2] 2
ON        1.D = 2.D AND 1.B = 2.B
LEFT JOIN [Table2] 3
ON        1.D = 3.D AND 2.B IS NULL

---

Related Topics