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