表中的所有数据
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 |