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')
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)
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.
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'))
how to add conditional where clause in sql
This can be achieved with conditional clauses.
$users = DB::table('users')
->where('aid', 2)
->when($cid, function ($query) {
$query->where('cid', 1);
})
->get();
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.
Related Topics
Use Google Bigquery to Build Histogram Graph
SQL - Pivot Table and Group by Not Working
How to Log All Failed SQL Statements in Oracle 10G
Add a Column That Represents a Concatenation of Two Other Varchar Columns
Insert Empty String into Int Column for SQL Server
Ms SQL Server Cross Table Constraint
SQL Server Fuzzy Search with Percentage of Match
Bulk Load Data Conversion Error (Truncation)
Column Order in Select * Statement - Guaranteed
Calculating Total Time Duration in MySQL
Using Guid in SQLite Select Where Guid Is Stored in the SQLite Db as Binaries
Retrieve Aggregates for Arbitrary Time Intervals
Do I Need to Call Rollback If I Never Commit
Does Liquibase Support Dry Run
Cumulative Sum of Values by Month, Filling in for Missing Months
A Constraint That Only Allows One of Two Tables to Reference a Base Table