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

CREATE PROCEDURE [dbo].[UP_TableEtp] 
(
    @etpYear int=0,
    @sqlWhere nvarchar(4000)='',
    @numStart int=0,
    @numEnd int = 20
)
AS

declare @SQL nvarchar(4000)
declare @YearThis int =year(getdate()) 
SET @SQL = ''
if(@etpYear=0)
begin
    set @etpYear=@YearThis
end

SET @SQL = @SQL + '
--存在
create table #EtpInTable(EtpYear varchar(10), EtpId int,EtpName varchar(50),Province varchar(50),City varchar(50),IdYear int,Total float)
insert into #EtpInTable select * from 
(
    select 
    '+convert(varchar,@etpYear)+' EtpYear,aa.Id EtpId, aa.Name EtpName,aa.Province,aa.City,rr.Id IdYear,rr.Total
    from table_Etp aa
    left join table_Rat rr on aa.Id=rr.Eid  
    left join table_Ind ind on ind.Id=aa.IndustryId
    where rr.Id='+right(@etpYear,2)+'
)a

--不存在
create table #EtpNoTable(EtpYear varchar(10), EtpId int,EtpName varchar(50),Province varchar(50),City varchar(50),IdYear int,Total float)
insert into #EtpNoTable select * from
(
    select 
    '+convert(varchar,@etpYear)+' EtpYear,aa.Id EtpId, aa.Name EtpName,aa.Province,aa.City,rr.Id IdYear,rr.Total
    from table_Etp aa
    left join #EtpInTable bb on bb.EtpId=aa.Id
    left join table_Ind ind on ind.Id=aa.IndustryId
    where aa.Id not in(select EtpId from #EtpInTable)
)b

--合并行
create table #EtpList(EtpYear varchar(10), EtpId int,EtpName varchar(50),Province varchar(50),City varchar(50),IdYear int,Total float)
insert into #EtpList select * from
(
    select * from #EtpInTable
    union
    select * from #EtpNoTable
)c

--合并行的row_numberID
select * from 
(
    select row_number() over (order by EtpId desc) AS Id,
    yy.Value SumValue,
    aa.*
    from #EtpList aa
    left join table_Year yy on yy.Eid=aa.EtpId and aa.IdYear=yy.Id
)m
where m.Id between '+convert(varchar,@numStart)+' and '+convert(varchar,@numEnd)+'
'

IF LEN(@sqlWhere) > 0
BEGIN
    SET @SQL = @SQL + @sqlWhere
END

SET @SQL = @SQL + '
    order by IdYear desc
    DROP TABLE #EtpInTable
    DROP TABLE #EtpNoTable
    DROP TABLE #EtpList
'            
EXEC sp_executesql @SQL