SQL中不同的参数使用不同的条件

表中的所有数据

L_Pats_In_Id	Dispensing_Indicator
1234	0
1234	1

在编写存储过程的时候有一需求，

当参数@GetOrderType=0的时候，取 Dispensing_Indicator=0的记录

当参数@GetOrderType=1的时候，取 Dispensing_Indicator>0的记录

SQL语句又不能这么写（报语法错误）：

DECLARE @GetOrderType INT=0 --0未执行，1已执行

SELECT*
FROM His_In_Med_Orders
WHERE L_Pats_In_Id=‘1234’
AND Drug_Storage=‘3013H’
if (@GetOrderType=0)
BEGIN
AND Dispensing_Indicator=0
END
else if(@GetOrderType=1)
BEGIN
Dispensing_Indicator>0
END

经过思考调试，可能用以下方法解决：

当GetOrderType=0时：

DECLARE @GetOrderType int =0 


SELECT L_Pats_In_Id,Dispensing_Indicator
FROM His_In_Med_Orders
WHERE L_Pats_In_Id='1234'
AND ((@GetOrderType=0 AND Dispensing_Indicator=0 ) or (@GetOrderType=1 AND Dispensing_Indicator>0 ))

得到的结果：

L_Pats_In_Id	Dispensing_Indicator
1234	0

当GetOrderType=1时：

DECLARE @GetOrderType int =1 


SELECT L_Pats_In_Id,Dispensing_Indicator
FROM His_In_Med_Orders
WHERE L_Pats_In_Id='1234'
AND ((@GetOrderType=0 AND Dispensing_Indicator=0 ) or (@GetOrderType=1 AND Dispensing_Indicator>0 ))

得到的结果：

L_Pats_In_Id	Dispensing_Indicator
1234	1