Conditional Where Clause in SQL Server

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)

How do I create a conditional WHERE clause?

Could you just do the following?

SELECT
    *
FROM
    Table
WHERE
    (@booleanResult = 1
    AND Column1 = 'value1')
OR
    (@booleanResult = 0
    AND Column1 = 'value1'
    AND Column2 = 'value2')

SQL Server : conditional in where statement using colunm as a condition

You don't need to write CASE WHEN here, you can simply change your WHERE to

where (@GroupID IS NULL OR a.groupid = @GroupID) AND (@Eoid IS NULL OR a.eoid=@Eoid)

This way you will get all records if both @GroupID & @Eoid are NULL and if specified it will match the records with respective ids.

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.

SQL - Conditional WHERE clause

Couple those with some OR statements:

SELECT *
FROM
    Customer c
WHERE
    c.StateID=@StateID
    AND ( c.CountyID=@CountyID OR @CountyID IS NULL )
    AND ( c.CityID=@CityID OR @CityID IS NULL )

For each of those parameters, if it is null, then the check is basically ignored.

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.

---

Related Topics