sql 存储过程 循环使用
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获取该病人及该项目的检验结果