USE [clab]
GO
/****** Object: StoredProcedure [dbo].[sp_bd_getResultByEcd] Script Date: 08/06/2014 16:47:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: zhangcy
-- Create date: 2014-07-09
-- Description: 传入病人ID及项目代码获取项目最新的检验结果
-- =============================================
CREATE PROCEDURE [dbo].[sp_bd_getResultByEcd]
(
@pat_in_no varchar(50) --病人id
,@itm_id_list varchar(500) --项目编码字符串,各项目编码以,进行分割
)
AS Declare @NeedParse varchar(500) --参数 没有处理的字符串 if (CharIndex(',', @itm_id_list)=0)
BEGIN
SELECT
a.res_itm_id as 项目id
,a.res_id as 报告id
,a.res_itm_ecd as 项目代码
,a.res_chr as 结果
,a.res_date as 检测时间
,a.value as 提示
from
(SELECT row_number() over (partition by res_itm_id order by res_date DESC) as rownum
,pat_in_no
,res_id
,res_itm_id
,res_itm_ecd
,res_chr
,res_date
,value
from patients with(nolock)
left join resulto with(nolock) on patients.pat_id=resulto.res_id
left join dict_res_ref_flag on dict_res_ref_flag.id = resulto.res_ref_flag
where pat_in_no=@pat_in_no
and pat_flag in (2,4)
and res_itm_id=@itm_id_list) as a
where a.rownum=1
END
else
BEGIN
set @NeedParse =@itm_id_list
while (charIndex(',', @NeedParse)>0)
begin
SELECT
a.res_itm_id as 项目id
,a.res_id as 报告id
,a.res_itm_ecd as 项目代码
,a.res_chr as 结果
,a.res_date as 检测时间
,a.value as 提示
from
(SELECT row_number() over (partition by res_itm_id order by res_date DESC) as rownum
,pat_in_no
,res_id
,res_itm_id
,res_itm_ecd
,res_chr
,res_date
,value
from patients with(nolock)
left join resulto with(nolock) on patients.pat_id=resulto.res_id
left join dict_res_ref_flag on dict_res_ref_flag.id = resulto.res_ref_flag
where pat_in_no=@pat_in_no
and pat_flag in (2,4)
and res_itm_id=SubString(@NeedParse,1,CharIndex(',',@NeedParse)-1)) as a
where a.rownum=1 set @NeedParse =SubString(@NeedParse,CharIndex(',', @NeedParse)+1,len(@NeedParse)-CharIndex(',', @NeedParse))
end SELECT
a.res_itm_id as 项目id
,a.res_id as 报告id
,a.res_itm_ecd as 项目代码
,a.res_chr as 结果
,a.res_date as 检测时间
,a.value as 提示
from
(SELECT row_number() over (partition by res_itm_id order by res_date DESC) as rownum
,pat_in_no
,res_id
,res_itm_id
,res_itm_ecd
,res_chr
,res_date
,value
from patients with(nolock)
left join resulto with(nolock) on patients.pat_id=resulto.res_id
left join dict_res_ref_flag on dict_res_ref_flag.id = resulto.res_ref_flag
where pat_in_no=@pat_in_no
and pat_flag in (2,4)
and res_itm_id=@NeedParse) as a
where a.rownum=1 END
GO
此存储过程为:传入病人ID,项目编码集,其中各编码以逗号分割。
存储过程里面要做的就是循环读取项目编码集里面的单个编码,及结合病人ID获取该病人及该项目的检验结果