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

时间:2022-06-27 11:51:46

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


3  

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


3  

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