想记录一下。USE [zy]GO/****** Object: StoredProcedure [dbo].[Pro_zdsfy_bldchtml] Script Date: 08/10/2016 08:47:55 ******/SET ANSI_NULLS ONGOSET Q ...
想记录一下。
USE [zy]GO/****** Object: StoredProcedure [dbo].[Pro_zdsfy_bldchtml] Script Date: 08/10/2016 08:47:55 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: <Author,,Name>-- Create date: <Create Date,,>-- Description: <Description,,>-- =============================================ALTER PROCEDURE [dbo].[Pro_zdsfy_bldchtml]@sqlwhere nvarchar(200)ASBEGIN create table #Temp1 ( ID int IDENTITY (1,1) not null, zyh varchar(20), rycs varchar(10), yy varchar(10), mm varchar(10), dd varchar(10), filename varchar(100), itemDesc nvarchar(100), cyrq datetime, rkrq datetime, rybq varchar(10) ) create table #Temp3 ( ID int IDENTITY (1,1) not null, zyh varchar(20), rycs varchar(10), yy varchar(10), mm varchar(10), dd varchar(10), filename varchar(100), itemDesc nvarchar(100), cyrq datetime, rkrq datetime, rybq varchar(10) ) declare @a bigint; declare @b bigint; declare @c bigint; declare @d bigint; declare @n bigint; set @a=1; set @b=1; set @c=0; set @d=1; set @n=1; create table #Temp2 ( ID int IDENTITY (1,1) not null, zyh varchar(20), rycs varchar(10), filenames nvarchar(1000) ) create table #Temp4 ( ID int IDENTITY (1,1) not null, zyh varchar(20), rycs varchar(10), filenames nvarchar(1000) ) insert into #temp1 select * from ( select a.zyh,a.rycs,year(a.ryrq) as yy,month(a.ryrq) as mm,day(a.ryrq) as dd,b.filename,b.itemDesc,c.cyrq,c.rkrq,c.rybq from brryxx as a left join epr_fileindex_sx as b on a.zyh=b.zyh and a.rycs=b.rycs left join yz_brxx as c on a.zyh=c.zyh and a.rycs=c.rycs where b.filename is not null and b.filename<>'' union select a.zyh,a.rycs,year(a.ryrq) as yy,month(a.ryrq) as mm,day(a.ryrq) as dd,b.filename,b.itemDesc,c.cyrq,c.rkrq,c.rybq from brryxx as a left join emr_fileindex_gd as b on a.zyh=b.zyh and a.rycs=b.rycs left join yz_brxx as c on a.zyh=c.zyh and a.rycs=c.rycs where b.filename is not null and b.filename<>'') as b select @b=count(*) from #temp1 insert into #temp2 select c.zyh,c.rycs,'' from (select zyh,rycs from #temp1 group by zyh,rycs) as c select @d=count(*) from #temp2 while(@d>@c) begin set @c=@c+1 declare @zyh nvarchar(30); declare @rycs varchar(10); declare @filenames nvarchar(1000); declare @sts nvarchar(1000); declare @t bigint; declare @m bigint; set @t=0; set @m=0; set @filenames=''; set @sts=''; select @zyh=zyh,@rycs=rycs from #temp2 where ID=@c select @t=count(*) from #temp1 where zyh=@zyh and rycs=@rycs insert into #Temp4 select zyh,rycs,filename from #temp1 where zyh=@zyh and rycs=@rycs if(@t>0) begin while(@t>@m) begin set @m=@m+1; select @sts=filenames from #Temp4 where ID=@m set @filenames=@filenames+@sts+','; end update #temp2 set filenames=@filenames where zyh=@zyh and rycs=@rycs end end select a.*,year(b.ryrq) as yy,month(b.ryrq) as mm,day(b.ryrq) as dd from #temp2 as a left join brryxx as b on a.zyh=b.zyh and a.rycs=b.rycs drop table #temp1 drop table #temp2 drop table #temp3 drop table #temp4END
原标题:存储过程记录一
关键词:
*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们:
admin#shaoqun.com
(#换成@)。