Conditional Operator in SQL Where Clause
You can do that without a case:
SELECT Team.teamID
FROM Team
WHERE (@teamid = 0 AND Team.teamID > 0)
OR (@teamid <> 0 AND Team.teamID = @teamid)
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 how to include conditional in where clause
This is a little more concise, that could be one line
(DATEPART(mm,cs.timestamp) = @month
OR (@accumulate = 1 AND DATEPART(mm, cs.timestamp) < @month))
or, with a nod to Sean's comment (which is good advice)
(DATEPART(MONTH,cs.timestamp) = @month
OR (@accumulate = 1 AND DATEPART(MONTH, cs.timestamp) < @month))
or, if you want a short but opaque and nasty bit of code (and @accumulate is a bit)
SIGN(@month - DATEPART(MONTH, cs.timestamp)) in (0, @accumulate)
Is there such thing as a conditional operator in SQL Server?
Is there such a thing as using an IF/ELSE in the WHERE clause
Absolutely, there is a CASE
expression - not only in the WHERE
clause, but also in other parts of your query. However, a more common approach is to use logical expressions in the WHERE
clause that force SQL Server to take one condition or the other depending on the parameter setting.
my condition would be
if @Customer = '' then select all
If you would like to select all customers when the parameter is set to empty, or select all customers where the parameter is not set, you can do this:
SELECT *
FROM SalesData
WHERE @Customer = ''
OR Customer = @Customer
If @Customer
is set to ''
, the first clause of the OR
expression will be TRUE
for all rows, so all customers would be returned. However, when @Customer
is non-empty, the second part of the expression would be evaluated.
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: IF clause within WHERE clause
Use a CASE statement
UPDATE: The previous syntax (as pointed out by a few people) doesn't work. You can use CASE as follows:
WHERE OrderNumber LIKE
CASE WHEN IsNumeric(@OrderNumber) = 1 THEN
@OrderNumber
ELSE
'%' + @OrderNumber
END
Or you can use an IF statement like @N. J. Reed points out.
Conditional Statements Inside A Where Clause
Try this: (I think I got your logic right...)
Cause you want it if the status is 1 for any database, but only want the status 3s for the databases that are not that single specified one...
Select ...
Where id = @my_id
And (status = 1 Or
(Db_Name() <> @DbName And status = 3))
Related Topics
SQL Select for All Records That May Holds Specific Value
Generic SQL That Both Access and Odbc/Oracle Can Understand
Time Based Priority in Active Record Query
Good Database and Structure to Store Synonyms
If It Is Not Allowed to Rollback a Truncate Statement Then How How to Use It in a Transaction
Datename(Month,Getadate()) Is Returning Numeric Value of the Month as '09'
Convert Datetime to Unix Epoch in Informix
How to Parse Xml Tags in Bigquery Standard SQL
Mysql, Delete Query with a Join