是否有可能在mssql的WHERE子句中使用IF子句?
例子:
WHERE
IF IsNumeric(@OrderNumber) = 1
OrderNumber = @OrderNumber
ELSE
OrderNumber LIKE '%' + @OrderNumber + '%'
是否有可能在mssql的WHERE子句中使用IF子句?
例子:
WHERE
IF IsNumeric(@OrderNumber) = 1
OrderNumber = @OrderNumber
ELSE
OrderNumber LIKE '%' + @OrderNumber + '%'
当前回答
您需要CASE语句
WHERE OrderNumber LIKE
CASE WHEN IsNumeric(@OrderNumber)=1 THEN @OrderNumber ELSE '%' + @OrderNumber END
其他回答
WHERE (IsNumeric(@OrderNumber) <> 1 OR OrderNumber = @OrderNumber) AND (IsNumber(@OrderNumber) = 1 OR OrderNumber LIKE '%' + @OrderNumber + '%')
//使用存储过程按国家和站点筛选用户的示例
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
//从输入的countryId和/或siteId如果存在,否则不过滤
您应该能够在没有任何IF或CASE的情况下做到这一点
WHERE
(IsNumeric(@OrderNumber) AND
(CAST OrderNumber AS VARCHAR) = (CAST @OrderNumber AS VARCHAR)
OR
(NOT IsNumeric(@OrderNumber) AND
OrderNumber LIKE ('%' + @OrderNumber))
根据SQL的风格,您可能需要将订单号上的类型转换调整为INT或VARCHAR,这取决于是否支持隐式类型转换。
这是WHERE子句中非常常见的技术。如果你想在WHERE子句中应用一些“If”逻辑,你所需要做的就是在需要应用它的部分中添加一个布尔AND的额外条件。
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
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
看看这是否有帮助。