对于嵌套的T-SQL可选选项，如果在这种情况下比较失败的话。

Is there a viable alternative in T-SQL to a nested IF ELSE for comparisons in which different logic must take place based on the result of the comparison? My situation is involves selecting a nvarchar value representing the condition for evaluation, and based on the condition an evaluation takes place. I initially sought to implement this in a CASE statement as follows:

在T-SQL中是否有一种可行的替代方法，如果在比较的结果的基础上进行不同的逻辑的比较，那么是否可以选择嵌套?我的情况是选择一个代表评估条件的nvarchar值，并根据条件进行评估。我最初设法在情况说明中执行这一点如下:

SELECT
            CASE @condition
            WHEN  '<' THEN

                IF (@processing_time < @threshold_value)
                BEGIN
                    SET @condition_met = 1 --compare threshold_value and processing_time
                END

            WHEN '<=' THEN
                    IF (@processing_time <= @threshold_value)
                    BEGIN
                        SET @condition_met = 1 --compare threshold_value and processing_time
                    END
                WHEN '>' THEN
                    IF (@processing_time > @threshold_value)
                    BEGIN
                        SET @condition_met = 1 --compare threshold_value and processing_time
                    END
                WHEN '>=' THEN
                    IF (@processing_time >= @threshold_value)
                    BEGIN
                        SET @condition_met = 1 --compare threshold_value and processing_time
                    END
                WHEN '=' THEN
                    IF (@processing_time = @threshold_value)
                    BEGIN
                        SET @condition_met = 1 --compare threshold_value and processing_time
                    END
            END -- end case statement

However, based on what I have seen elsewhere and the syntax errors I am getting, it seems that the WHEN clause in a case statement can only assign values, not perform evaluation logic. The only alternative I can think of is to perform equivalent logic using nested IF/ELSE statements. IS there a better way? Redesign of tables/data types is an option.

然而，根据我在其他地方看到的以及我得到的语法错误，似乎case语句中的WHEN子句只能分配值，而不能执行评估逻辑。我能想到的唯一替代方法是使用嵌套IF/ELSE语句执行等效逻辑。有更好的方法吗?重新设计表/数据类型是一种选择。

3 个解决方案

#1

I haven't got a database here to check this syntax hasn't got typos in it but this should do it..

我这里没有数据库来检查这个语法里面没有拼写错误，但是这个应该可以。

set @condition_met = (SELECT 
                        CASE WHEN (@condition = '<' AND @processing_time < @threshold_value) OR
                                  (@condition = '<=' AND @processing_time <= @threshold_value) OR
                                  (@condition = '>' AND @processing_time > @threshold_value) OR
                                  (@condition = '>=' AND @processing_time >= @threshold_value) OR
                                  (@condition = '=' AND @processing_time = @threshold_value) 
                             THEN 1 ELSE 0 END)

Alternatively you could simply do a series of IF statements to do the same thing, E.g.

或者你也可以做一系列IF语句来做同样的事情，例如。

SET @condition_met = 0;
IF @condition = "<" AND @processing_time < @threshold_value SET @condition_met = 1;
IF @condition = "<=" AND @processing_time <= @threshold_value SET @condition_met = 1;
etc..

#2

SELECT @Condition_Met = CASE 
            WHEN (@condition = '<' AND @processing_time < @threshold_value) OR
                 (@condition = '<=' AND @processing_time <= @threshold_value) OR
                 (@condition = '>' AND @processing_time > @threshold_value) OR
                 (@condition = '>=' AND @processing_time >= @threshold_value) OR
                 (@condition = '=' AND @processing_time = @threshold_value) 
                 THEN 1 ELSE 0 END

It seems to me like you're trying to do too much procedural code, and not enough set-based or declarative. I have a feeling this whole thing belongs as part of a larger query such that you don't even need to declare some of these variables.

在我看来，您似乎试图做太多的过程性代码，而没有足够的基于集合或声明性代码。我有一种感觉，这整个东西属于一个更大的查询的一部分，这样您甚至不需要声明其中的一些变量。

#3

Consider also dynamic SQL solution.

还要考虑动态SQL解决方案。

declare @op varchar(5) = '>'
declare @sql nvarchar(max) = 
'declare @condition_met bit
declare @processing_time int = 7
declare @threshold_value int = 6

select @condition_met = case when @processing_time'+@op+'@threshold_value then 1 else 0 end
select @condition_met'
exec (@sql)

It could be useful, if expression is in the WHERE clause and either @processing_time or @threshold_value is an indexed column and you really want to use index on it.

如果表达式在WHERE子句中，并且@processing_time或@threshold d_value是一个索引列，并且您确实希望在其上使用索引，那么它可能是有用的。

#1