是否有可能在mssql的WHERE子句中使用IF子句?

例子:

WHERE
    IF IsNumeric(@OrderNumber) = 1
        OrderNumber = @OrderNumber
    ELSE
        OrderNumber LIKE '%' + @OrderNumber + '%'

当前回答

    WHERE OrderNumber LIKE CASE WHEN IsNumeric(@OrderNumber) = 1 THEN @OrderNumber ELSE  '%' + @OrderNumber END

In line case条件将正常工作。

其他回答

//使用存储过程按国家和站点筛选用户的示例

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如果存在,否则不过滤

您需要CASE语句

WHERE OrderNumber LIKE
CASE WHEN IsNumeric(@OrderNumber)=1 THEN @OrderNumber ELSE '%' + @OrderNumber END

我认为在……情况下……可以使用布尔值。我正在使用T-SQL。

场景:假设如果bool为假,你想获取Person-30的爱好,如果bool为真,你想获取Person-42的爱好。(根据一些人的说法,业余爱好查找占了90%以上的商业计算周期,所以要密切关注。)

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;

在sql server中,我有同样的问题,我想使用一个和语句,只有当参数为假,对真,我必须显示两个值真和假,所以我用这种方式使用它

(T.IsPublic = @ShowPublic or  @ShowPublic = 1)

下面的示例将查询作为布尔表达式的一部分执行,然后根据布尔表达式的结果执行略有不同的语句块。每个语句块以BEGIN开始,以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

使用嵌套IF…其他语句 下面的例子展示了IF…ELSE语句如何嵌套在另一个语句中。将@Number变量设置为5、50和500,以测试每条语句。

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