T-SQL - 在where和子句中使用参数

I want to pass a parameter and depending on its value I want to evaluate a condition like this:

我想传递一个参数，根据它的值，我想评估一个这样的条件：

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
DECLARE @Operation INTEGER = 0;

WITH CTEMycte AS
(
    SELECT   
        Table1.Field1,
        Table1.Field2,Table1.Field3
    FROM     
        Table1
    WHERE    
        Table1.Field2 = 1
)
SELECT Field1
FROM CTEMycte
WHERE somecondtion 
  AND 
    CASE WHEN @Operation = 0 THEN [CTEMycte].[Field3] >= 100
         WHEN @Operation = 1 THEN [CTEMycte].[Field3] <= 100 
         WHEN @Operation = 2 THEN [CTEMycte].[Field3] = 100 
    END

1 个解决方案

#1

You cannot have condition after THEN.You can use OR:

这之后你不能有条件。你可以使用OR：

WHERE somecondtion 
  AND (
   (@Operation = 0 AND [CTEMycte].[Field3] >= 100)
   OR
   (@Operation = 1 AND [CTEMycte].[Field3] <= 100) 
   OR
   (@Operation = 2 AND [CTEMycte].[Field3] = 100 )
  )

Alternatively you can combine condition in WHEN part:

或者你可以在WHEN部分组合条件：

WHERE somecondtion AND
     (CASE WHEN @Operation = 0 AND [CTEMycte].[Field3] >= 100 THEN 1
           WHEN @Operation = 1 AND [CTEMycte].[Field3] <= 100 THEN 1
           WHEN @Operation = 2 AND [CTEMycte].[Field3] = 100  THEN 1
           ELSE 0
      END) = 1

#1