How to Create a Conditional Where Clause

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.

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')

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 EF Core

You need to capture the result of the Where(...) and store it in a (new) variable.

public List<Comments> GetCommentsByResource(string ResourceName, Guid ResourceID)
{
    var query = CommentsManager.GetQueryable();

    switch (ResourceName)
    {
        case "Bike":
            query = query.Where(x => x.BikeID == ResourceID);
        //  ^^^^^^^
            break;
        case "Skis":
            query = query.Where(x => x.SkiID == ResourceID);
            break;
        case "Helmet":
            query = query.Where(x => x.HelmetID == ResourceID);
            break;
        case "Scooter":
            query = query.Where(x => x.ScooterID == ResourceID);
            break;
    }

    return query.OrderByDescending(o => o.CreatedOn).ToList();
}

You need to check if the result type of the where matches the result type of CommentsManager.GetQueryable();. You might explicitly declare the type instead of var query.

Complex Conditional Query in Where Clause

There's a conceptual problem to address here first, and then we can look at how to actually do what you want.

select is starting a statement here. Statements end with a semicolon. You can't put statements inside other statements. What you are doing is the same as trying to do something like this in c#:

int i = if (true) 1; else 2;;

You can of course use expressions inside statements:

int i = true ? 1 : 2;

Moreover, select is a declarative statement. You can't do imperative flow of control inside a declarative language construct. You're mixing metaphors, as it were. To understand the declarative/imperative distinction see this question and in particular (in my opinion) this answer.

So the first thing to do is wrap your head around the declarative nature of SQL statements like select. Yes, T-SQL also includes imperative constructs like if and while, but you can't do imperative inside declarative.

You can use conditional expressions (and other expressions) inside a declarative statement:

select name,
       case 
         when name = 'date' then 'this is the date row'
         else 'this is not the date row'
       end
from   sys.types;

In this example the declarative select says what to do with all of the rows returned by the from clause. I don't write a while loop or a for loop in order to instruct the computer to loop over each row and provide instructions inside the loop. The from returns all the rows, and the select declares what I want to do with each of them. The case expression will be evaluated against every row in sys.types.

OK, so what about your specific question? There's many ways to write the code. Here is one way that is very similar to your current structure. First I conditionally (imperatively!) populate a temp table with the statuses I want. Then I declaratively use that temp table as my filter:

create table #statuses 
(
   statusname varchar(32)
);

declare @dept nvarchar(12) = 'IT';

if (@dept = 'IT') 
begin
   insert #statuses (statusname) values
   ('ADStat1'), ('PurchStat1'), ('PurchStat2'), ('PurchStat3'), ('PurchStat4'), ('ITStat1'), ('ITStat2');
end 
else if (@dept  = 'PURCH')
begin
   insert #statuses (statusname) values 
   ('PurchStat1'), ('PurchStat2'), ('PurchStat3'), ('PurchStat4');
end 
else if (@dept = 'ADMIN')
begin
   insert #statuses (statusname) values
   ('ADStat1');
end

select   *
from     tbl_orders
where    ord_status in (select statusName from #statuses);

Can I do it without the temp table? Sure. Here's one way:

declare @dept nvarchar(12) = 'IT';

select   *
from     tbl_orders
where    (@dept = 'ADMIN' and ord_status = 'ADStat1')
         or (@dept = 'PURCH' and ord_status in ('PurchStat1', 'PurchStat2', 'PurchStat3', 'PurchStat4'))
         or (@dept = 'IT' and ord_status in ('ADStat1', 'PurchStat1', 'PurchStat2', 'PurchStat3', 'PurchStat4', 'ITStat1', 'ITStat2'));

Here we evaluate a different in depending on the value of @dept. Clearly only one of them actually needs to be evaluated, and the other two don't really need to be there, depending on which value of @dept is provided. Adding an option (recompile) can be beneficial in cases like this. For more information about option (recompile) look here and here.

Conditional where clause in Oracle SQL query is not working

The solution was to transform the Date in String, and in the select do this
and (:endDate is null or pl.date_end <= TO_DATE(:endDate , 'yyyy-MM-dd HH:MI:SS'))

Where Clause Using Conditional Statement

The cleanest way I'm implemented these kind of crazy rules is a
holderTable
and a countVariable against the holder table.

I'll give a generic examples.
This is a "approach" and "philosophy", not a specific answer....with complex WHERE clauses.

DECLARE @customerCountryCount int

DECLARE @customerCountry TABLE ( CountryName varchar(15) )

if ( "the moon is blue on tuesday" ) /* << whatever rules you have */
BEGIN 
    INSERT INTO @customerCountry SELECT "Honduras" UNION ALL SELECT "Malaysia"
END

if ( "favorite color = green" ) /* << whatever rules you have */
BEGIN 
    INSERT INTO @customerCountry SELECT "Greenland" UNION ALL SELECT "Peru"
END

SELECT @customerCountryCount = COUNT(*) FROM @customerCountry

Select * from dbo.Customers c 
WHERE

(@customerCountryCount = 0) 
OR
( exists (select null from @customerCountry innerVariableTable where UPPER(innerVariableTable.CountryName) = UPPER(c.Country)  ))
) 

This way, instead of putting all the "twisted logic" in an overly complex WHERE statement..... you have "separation of concerns"...

Your inserts into @customerCountry are separated from your use of @customerCountry.

And you have the @customerCountryCount "trick" to distinguish when nothing was used.

You can add a @customerCountryNotExists table as well, and code it to where not exists.

As a side note, you may want to try using a #temp table (instead of a @variabletable (@customerCountry above)... and performance test these 2 options.
There is no "single answer". You have to "try it out".
And many many variables go into #temp table performance (from a sql-server SETUP, not "how you code a stored procedure". That is way outside the scope of this question.

Here is a SOF link to "safe" #temp table usage.

Temporary table in SQL server causing ' There is already an object named' error

Sql where clause, Get All if value is empty or null else use like statement

Your code can be updated to this:

SELECT [Category]
FROM [dbo].[Records]
WHERE (@SearchText IS NULL OR (Title like '%' + ISNULL( @SearchText ,'')+ '%'))

If you feed null then first condition will be true else second.

---

Related Topics