Is it possible to use an IF clause within a WHERE clause in MS SQL?
Example:
WHERE
IF IsNumeric(@OrderNumber) = 1
OrderNumber = @OrderNumber
ELSE
OrderNumber LIKE '%' + @OrderNumber + '%'
This question is related to
sql
sql-server
tsql
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.
You should be able to do this without any IF or CASE
WHERE
(IsNumeric(@OrderNumber) AND
(CAST OrderNumber AS VARCHAR) = (CAST @OrderNumber AS VARCHAR)
OR
(NOT IsNumeric(@OrderNumber) AND
OrderNumber LIKE ('%' + @OrderNumber))
Depending on the flavour of SQL you may need to tweak the casts on the order number to an INT or VARCHAR depending on whether implicit casts are supported.
This is a very common technique in a WHERE clause. If you want to apply some "IF" logic in the WHERE clause all you need to do is add the extra condition with an boolean AND to the section where it needs to be applied.
You should be able to do this without any IF or CASE
WHERE
(IsNumeric(@OrderNumber) AND
(CAST OrderNumber AS VARCHAR) = (CAST @OrderNumber AS VARCHAR)
OR
(NOT IsNumeric(@OrderNumber) AND
OrderNumber LIKE ('%' + @OrderNumber))
Depending on the flavour of SQL you may need to tweak the casts on the order number to an INT or VARCHAR depending on whether implicit casts are supported.
This is a very common technique in a WHERE clause. If you want to apply some "IF" logic in the WHERE clause all you need to do is add the extra condition with an boolean AND to the section where it needs to be applied.
You should be able to do this without any IF or CASE
WHERE
(IsNumeric(@OrderNumber) AND
(CAST OrderNumber AS VARCHAR) = (CAST @OrderNumber AS VARCHAR)
OR
(NOT IsNumeric(@OrderNumber) AND
OrderNumber LIKE ('%' + @OrderNumber))
Depending on the flavour of SQL you may need to tweak the casts on the order number to an INT or VARCHAR depending on whether implicit casts are supported.
This is a very common technique in a WHERE clause. If you want to apply some "IF" logic in the WHERE clause all you need to do is add the extra condition with an boolean AND to the section where it needs to be applied.
You should be able to do this without any IF or CASE
WHERE
(IsNumeric(@OrderNumber) AND
(CAST OrderNumber AS VARCHAR) = (CAST @OrderNumber AS VARCHAR)
OR
(NOT IsNumeric(@OrderNumber) AND
OrderNumber LIKE ('%' + @OrderNumber))
Depending on the flavour of SQL you may need to tweak the casts on the order number to an INT or VARCHAR depending on whether implicit casts are supported.
This is a very common technique in a WHERE clause. If you want to apply some "IF" logic in the WHERE clause all you need to do is add the extra condition with an boolean AND to the section where it needs to be applied.
You don't need a IF statement at all.
WHERE
(IsNumeric(@OrderNumber) = 1 AND OrderNumber = @OrderNumber)
OR (IsNumeric(@OrderNumber) = 0 AND OrderNumber LIKE '%' + @OrderNumber + '%')
You don't need a IF statement at all.
WHERE
(IsNumeric(@OrderNumber) = 1 AND OrderNumber = @OrderNumber)
OR (IsNumeric(@OrderNumber) = 0 AND OrderNumber LIKE '%' + @OrderNumber + '%')
There isn't a good way to do this in SQL. Some approaches I have seen:
1) Use CASE combined with boolean operators:
WHERE
OrderNumber = CASE
WHEN (IsNumeric(@OrderNumber) = 1)
THEN CONVERT(INT, @OrderNumber)
ELSE -9999 -- Some numeric value that just cannot exist in the column
END
OR
FirstName LIKE CASE
WHEN (IsNumeric(@OrderNumber) = 0)
THEN '%' + @OrderNumber
ELSE ''
END
2) Use IF's outside the SELECT
IF (IsNumeric(@OrderNumber)) = 1
BEGIN
SELECT * FROM Table
WHERE @OrderNumber = OrderNumber
END ELSE BEGIN
SELECT * FROM Table
WHERE OrderNumber LIKE '%' + @OrderNumber
END
3) Using a long string, compose your SQL statement conditionally, and then use EXEC
The 3rd approach is hideous, but it's almost the only think that works if you have a number of variable conditions like that.
There isn't a good way to do this in SQL. Some approaches I have seen:
1) Use CASE combined with boolean operators:
WHERE
OrderNumber = CASE
WHEN (IsNumeric(@OrderNumber) = 1)
THEN CONVERT(INT, @OrderNumber)
ELSE -9999 -- Some numeric value that just cannot exist in the column
END
OR
FirstName LIKE CASE
WHEN (IsNumeric(@OrderNumber) = 0)
THEN '%' + @OrderNumber
ELSE ''
END
2) Use IF's outside the SELECT
IF (IsNumeric(@OrderNumber)) = 1
BEGIN
SELECT * FROM Table
WHERE @OrderNumber = OrderNumber
END ELSE BEGIN
SELECT * FROM Table
WHERE OrderNumber LIKE '%' + @OrderNumber
END
3) Using a long string, compose your SQL statement conditionally, and then use EXEC
The 3rd approach is hideous, but it's almost the only think that works if you have a number of variable conditions like that.
There isn't a good way to do this in SQL. Some approaches I have seen:
1) Use CASE combined with boolean operators:
WHERE
OrderNumber = CASE
WHEN (IsNumeric(@OrderNumber) = 1)
THEN CONVERT(INT, @OrderNumber)
ELSE -9999 -- Some numeric value that just cannot exist in the column
END
OR
FirstName LIKE CASE
WHEN (IsNumeric(@OrderNumber) = 0)
THEN '%' + @OrderNumber
ELSE ''
END
2) Use IF's outside the SELECT
IF (IsNumeric(@OrderNumber)) = 1
BEGIN
SELECT * FROM Table
WHERE @OrderNumber = OrderNumber
END ELSE BEGIN
SELECT * FROM Table
WHERE OrderNumber LIKE '%' + @OrderNumber
END
3) Using a long string, compose your SQL statement conditionally, and then use EXEC
The 3rd approach is hideous, but it's almost the only think that works if you have a number of variable conditions like that.
There isn't a good way to do this in SQL. Some approaches I have seen:
1) Use CASE combined with boolean operators:
WHERE
OrderNumber = CASE
WHEN (IsNumeric(@OrderNumber) = 1)
THEN CONVERT(INT, @OrderNumber)
ELSE -9999 -- Some numeric value that just cannot exist in the column
END
OR
FirstName LIKE CASE
WHEN (IsNumeric(@OrderNumber) = 0)
THEN '%' + @OrderNumber
ELSE ''
END
2) Use IF's outside the SELECT
IF (IsNumeric(@OrderNumber)) = 1
BEGIN
SELECT * FROM Table
WHERE @OrderNumber = OrderNumber
END ELSE BEGIN
SELECT * FROM Table
WHERE OrderNumber LIKE '%' + @OrderNumber
END
3) Using a long string, compose your SQL statement conditionally, and then use EXEC
The 3rd approach is hideous, but it's almost the only think that works if you have a number of variable conditions like that.
Use a CASE statement instead of IF.
Use a CASE statement instead of IF.
Use a CASE statement instead of IF.
Use a CASE statement instead of IF.
You want the CASE statement
WHERE OrderNumber LIKE
CASE WHEN IsNumeric(@OrderNumber)=1 THEN @OrderNumber ELSE '%' + @OrderNumber END
You want the CASE statement
WHERE OrderNumber LIKE
CASE WHEN IsNumeric(@OrderNumber)=1 THEN @OrderNumber ELSE '%' + @OrderNumber END
You want the CASE statement
WHERE OrderNumber LIKE
CASE WHEN IsNumeric(@OrderNumber)=1 THEN @OrderNumber ELSE '%' + @OrderNumber END
You want the CASE statement
WHERE OrderNumber LIKE
CASE WHEN IsNumeric(@OrderNumber)=1 THEN @OrderNumber ELSE '%' + @OrderNumber END
I think that where...like/=...case...then... can work with Booleans. I am using T-SQL.
Scenario: Let's say you want to get Person-30's hobbies if bool is false, and Person-42's hobbies if bool is true. (According to some, hobby-lookups comprise over 90% of business computation cycles, so pay close attn.).
CREATE PROCEDURE sp_Case
@bool bit
AS
SELECT Person.Hobbies
FROM Person
WHERE Person.ID =
case @bool
when 0
then 30
when 1
then 42
end;
I think that where...like/=...case...then... can work with Booleans. I am using T-SQL.
Scenario: Let's say you want to get Person-30's hobbies if bool is false, and Person-42's hobbies if bool is true. (According to some, hobby-lookups comprise over 90% of business computation cycles, so pay close attn.).
CREATE PROCEDURE sp_Case
@bool bit
AS
SELECT Person.Hobbies
FROM Person
WHERE Person.ID =
case @bool
when 0
then 30
when 1
then 42
end;
WHERE (IsNumeric(@OrderNumber) <> 1 OR OrderNumber = @OrderNumber)
AND (IsNumber(@OrderNumber) = 1 OR OrderNumber LIKE '%'
+ @OrderNumber + '%')
WHERE (IsNumeric(@OrderNumber) <> 1 OR OrderNumber = @OrderNumber)
AND (IsNumber(@OrderNumber) = 1 OR OrderNumber LIKE '%'
+ @OrderNumber + '%')
CASE Statement is better option than IF always.
WHERE vfl.CreatedDate >= CASE WHEN @FromDate IS NULL THEN vfl.CreatedDate ELSE @FromDate END
AND vfl.CreatedDate<=CASE WHEN @ToDate IS NULL THEN vfl.CreatedDate ELSE @ToDate END
CASE Statement is better option than IF always.
WHERE vfl.CreatedDate >= CASE WHEN @FromDate IS NULL THEN vfl.CreatedDate ELSE @FromDate END
AND vfl.CreatedDate<=CASE WHEN @ToDate IS NULL THEN vfl.CreatedDate ELSE @ToDate END
WHERE OrderNumber LIKE CASE WHEN IsNumeric(@OrderNumber) = 1 THEN @OrderNumber ELSE '%' + @OrderNumber END
In line case Condition will work properly.
WHERE OrderNumber LIKE CASE WHEN IsNumeric(@OrderNumber) = 1 THEN @OrderNumber ELSE '%' + @OrderNumber END
In line case Condition will work properly.
The following example executes a query as part of the Boolean expression and then executes slightly different statement blocks based on the result of the Boolean expression. Each statement block starts with BEGIN and completes with END.
USE AdventureWorks2012;
GO
DECLARE @AvgWeight decimal(8,2), @BikeCount int
IF
(SELECT COUNT(*) FROM Production.Product WHERE Name LIKE 'Touring-3000%' ) > 5
BEGIN
SET @BikeCount =
(SELECT COUNT(*)
FROM Production.Product
WHERE Name LIKE 'Touring-3000%');
SET @AvgWeight =
(SELECT AVG(Weight)
FROM Production.Product
WHERE Name LIKE 'Touring-3000%');
PRINT 'There are ' + CAST(@BikeCount AS varchar(3)) + ' Touring-3000 bikes.'
PRINT 'The average weight of the top 5 Touring-3000 bikes is ' + CAST(@AvgWeight AS varchar(8)) + '.';
END
ELSE
BEGIN
SET @AvgWeight =
(SELECT AVG(Weight)
FROM Production.Product
WHERE Name LIKE 'Touring-3000%' );
PRINT 'Average weight of the Touring-3000 bikes is ' + CAST(@AvgWeight AS varchar(8)) + '.' ;
END ;
GO
Using nested IF...ELSE statements The following example shows how an IF … ELSE statement can be nested inside another. Set the @Number variable to 5, 50, and 500 to test each statement.
DECLARE @Number int
SET @Number = 50
IF @Number > 100
PRINT 'The number is large.'
ELSE
BEGIN
IF @Number < 10
PRINT 'The number is small'
ELSE
PRINT 'The number is medium'
END ;
GO
The following example executes a query as part of the Boolean expression and then executes slightly different statement blocks based on the result of the Boolean expression. Each statement block starts with BEGIN and completes with END.
USE AdventureWorks2012;
GO
DECLARE @AvgWeight decimal(8,2), @BikeCount int
IF
(SELECT COUNT(*) FROM Production.Product WHERE Name LIKE 'Touring-3000%' ) > 5
BEGIN
SET @BikeCount =
(SELECT COUNT(*)
FROM Production.Product
WHERE Name LIKE 'Touring-3000%');
SET @AvgWeight =
(SELECT AVG(Weight)
FROM Production.Product
WHERE Name LIKE 'Touring-3000%');
PRINT 'There are ' + CAST(@BikeCount AS varchar(3)) + ' Touring-3000 bikes.'
PRINT 'The average weight of the top 5 Touring-3000 bikes is ' + CAST(@AvgWeight AS varchar(8)) + '.';
END
ELSE
BEGIN
SET @AvgWeight =
(SELECT AVG(Weight)
FROM Production.Product
WHERE Name LIKE 'Touring-3000%' );
PRINT 'Average weight of the Touring-3000 bikes is ' + CAST(@AvgWeight AS varchar(8)) + '.' ;
END ;
GO
Using nested IF...ELSE statements The following example shows how an IF … ELSE statement can be nested inside another. Set the @Number variable to 5, 50, and 500 to test each statement.
DECLARE @Number int
SET @Number = 50
IF @Number > 100
PRINT 'The number is large.'
ELSE
BEGIN
IF @Number < 10
PRINT 'The number is small'
ELSE
PRINT 'The number is medium'
END ;
GO
In sql server I had same problem I wanted to use an and statement only if parameter is false and on true I had to show both values true and false so I used it this way
(T.IsPublic = @ShowPublic or @ShowPublic = 1)
In sql server I had same problem I wanted to use an and statement only if parameter is false and on true I had to show both values true and false so I used it this way
(T.IsPublic = @ShowPublic or @ShowPublic = 1)
To clarify some of the logical equivalence solutions.
An if statement
if (a) then b
is logically equivalent to
(!a || b)
It's the first line on the Logical equivalences involving conditional statements section of the Logical equivalence wikipedia article.
To include the else, all you would do is add another conditional
if(a) then b;
if(!a) then c;
which is logically equivalent to (!a || b) && (a || c)
So using the OP as an example:
IF IsNumeric(@OrderNumber) = 1
OrderNumber = @OrderNumber
ELSE
OrderNumber LIKE '%' + @OrderNumber + '%'
the logical equivalent would be:
(IsNumeric(@OrderNumber) <> 1 OR OrderNumber = @OrderNumber)
AND (IsNumeric(@OrderNumber) = 1 OR OrderNumber LIKE '%' + @OrderNumber + '%' )
To clarify some of the logical equivalence solutions.
An if statement
if (a) then b
is logically equivalent to
(!a || b)
It's the first line on the Logical equivalences involving conditional statements section of the Logical equivalence wikipedia article.
To include the else, all you would do is add another conditional
if(a) then b;
if(!a) then c;
which is logically equivalent to (!a || b) && (a || c)
So using the OP as an example:
IF IsNumeric(@OrderNumber) = 1
OrderNumber = @OrderNumber
ELSE
OrderNumber LIKE '%' + @OrderNumber + '%'
the logical equivalent would be:
(IsNumeric(@OrderNumber) <> 1 OR OrderNumber = @OrderNumber)
AND (IsNumeric(@OrderNumber) = 1 OR OrderNumber LIKE '%' + @OrderNumber + '%' )
// an example for using a stored procedure to select users filtered by country and site
CREATE STORED PROCEDURE GetUsers
@CountryId int = null,
@SiteId int = null
AS
BEGIN
SELECT *
FROM users
WHERE
CountryId = CASE WHEN ISNUMERIC(@CountryId) = 1 THEN @CountryId ELSE CountryId END AND
SiteId = CASE WHEN ISNUMERIC(@SiteId) = 1 THEN @SiteId ELSE SiteId END;
END
// an example for using a stored procedure to select users filtered by country and site
CREATE STORED PROCEDURE GetUsers
@CountryId int = null,
@SiteId int = null
AS
BEGIN
SELECT *
FROM users
WHERE
CountryId = CASE WHEN ISNUMERIC(@CountryId) = 1 THEN @CountryId ELSE CountryId END AND
SiteId = CASE WHEN ISNUMERIC(@SiteId) = 1 THEN @SiteId ELSE SiteId END;
END
If @LstTransDt is Null
begin
Set @OpenQty=0
end
else
begin
Select @OpenQty=IsNull(Sum(ClosingQty),0)
From ProductAndDepotWiseMonitoring
Where Pcd=@PCd And PtpCd=@PTpCd And TransDt=@LstTransDt
end
See if this helps.
If @LstTransDt is Null
begin
Set @OpenQty=0
end
else
begin
Select @OpenQty=IsNull(Sum(ClosingQty),0)
From ProductAndDepotWiseMonitoring
Where Pcd=@PCd And PtpCd=@PTpCd And TransDt=@LstTransDt
end
See if this helps.
USE AdventureWorks2012;
GO
IF
(SELECT COUNT(*) FROM Production.Product WHERE Name LIKE 'Touring-3000%' ) > 5
PRINT 'There are more than 5 Touring-3000 bicycles.'
ELSE PRINT 'There are 5 or less Touring-3000 bicycles.' ;
GO
USE AdventureWorks2012;
GO
IF
(SELECT COUNT(*) FROM Production.Product WHERE Name LIKE 'Touring-3000%' ) > 5
PRINT 'There are more than 5 Touring-3000 bicycles.'
ELSE PRINT 'There are 5 or less Touring-3000 bicycles.' ;
GO
Source: Stackoverflow.com