是否有可能在mssql的WHERE子句中使用IF子句?
例子:
WHERE
IF IsNumeric(@OrderNumber) = 1
OrderNumber = @OrderNumber
ELSE
OrderNumber LIKE '%' + @OrderNumber + '%'
当前回答
CASE语句总是比IF语句更好的选择。
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 (IsNumeric(@OrderNumber) <> 1 OR OrderNumber = @OrderNumber)
AND (IsNumber(@OrderNumber) = 1 OR OrderNumber LIKE '%'
+ @OrderNumber + '%')
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
您需要CASE语句
WHERE OrderNumber LIKE
CASE WHEN IsNumeric(@OrderNumber)=1 THEN @OrderNumber ELSE '%' + @OrderNumber END
WHERE OrderNumber LIKE CASE WHEN IsNumeric(@OrderNumber) = 1 THEN @OrderNumber ELSE '%' + @OrderNumber END
In line case条件将正常工作。
使用CASE语句而不是IF语句。
