sql开发参考,存储过程创建1

 1 CREATE PROCEDURE [dbo].[UP_TableEtp] 
 2 (
 3     @etpYear int=0,
 4     @sqlWhere nvarchar(4000)='',
 5     @numStart int=0,
 6     @numEnd int = 20
 7 )
 8 AS
 9 
10 declare @SQL nvarchar(4000)
11 declare @YearThis int =year(getdate()) 
12 SET @SQL = ''
13 if(@etpYear=0)
14 begin
15     set @etpYear=@YearThis
16 end
17 
18 SET @SQL = @SQL + '
19 --存在
20 create table #EtpInTable(EtpYear varchar(10), EtpId int,EtpName varchar(50),Province varchar(50),City varchar(50),IdYear int,Total float)
21 insert into #EtpInTable select * from 
22 (
23     select 
24     '+convert(varchar,@etpYear)+' EtpYear,aa.Id EtpId, aa.Name EtpName,aa.Province,aa.City,rr.Id IdYear,rr.Total
25     from table_Etp aa
26     left join table_Rat rr on aa.Id=rr.Eid  
27     left join table_Ind ind on ind.Id=aa.IndustryId
28     where rr.Id='+right(@etpYear,2)+'
29 )a
30 
31 --不存在
32 create table #EtpNoTable(EtpYear varchar(10), EtpId int,EtpName varchar(50),Province varchar(50),City varchar(50),IdYear int,Total float)
33 insert into #EtpNoTable select * from
34 (
35     select 
36     '+convert(varchar,@etpYear)+' EtpYear,aa.Id EtpId, aa.Name EtpName,aa.Province,aa.City,rr.Id IdYear,rr.Total
37     from table_Etp aa
38     left join #EtpInTable bb on bb.EtpId=aa.Id
39     left join table_Ind ind on ind.Id=aa.IndustryId
40     where aa.Id not in(select EtpId from #EtpInTable)
41 )b
42 
43 --合并行
44 create table #EtpList(EtpYear varchar(10), EtpId int,EtpName varchar(50),Province varchar(50),City varchar(50),IdYear int,Total float)
45 insert into #EtpList select * from
46 (
47     select * from #EtpInTable
48     union
49     select * from #EtpNoTable
50 )c
51 
52 --合并行的row_numberID
53 select * from 
54 (
55     select row_number() over (order by EtpId desc) AS Id,
56     yy.Value SumValue,
57     aa.*
58     from #EtpList aa
59     left join table_Year yy on yy.Eid=aa.EtpId and aa.IdYear=yy.Id
60 )m
61 where m.Id between '+convert(varchar,@numStart)+' and '+convert(varchar,@numEnd)+'
62 '
63 
64 IF LEN(@sqlWhere) > 0
65 BEGIN
66     SET @SQL = @SQL + @sqlWhere
67 END
68 
69 SET @SQL = @SQL + '
70     order by IdYear desc
71     DROP TABLE #EtpInTable
72     DROP TABLE #EtpNoTable
73     DROP TABLE #EtpList
74 '            
75 EXEC sp_executesql @SQL

 

转载于:https://www.cnblogs.com/you316/p/8506009.html