I am creating a SQL query in which I need a conditional where
clause.
It should be something like 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
--This is where i need the conditional clause
AND CASE
WHEN @JobsOnHold = 1 THEN DateAppr >= 0
ELSE DateAppr != 0
END
The above query is not working. Is this not the correct syntax or is there another way to do this that I don't know?
I don't want to use dynamic SQL, so is there any other way or do I have to use a workaround like using if else
and using the same query with different where
clauses?
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)
)