你的位置:首页 > 数据库

[数据库]存储过程记录一


想记录一下。

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

冥冥之中,只有注定。