USE [clab]GO/****** Object:  StoredProcedure [dbo].[sp_bd_getResultByEcd]    Script Date: 08/06/2014 16:47:55 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- 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) --项目编码字符串，各项目编码以,进行分割    )ASDeclare @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    ENDelse    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                        ENDGO