谁帮我把下面的SQL语句修改成存储过程,或是新增一个函数?谢谢大家了!!!急用!
declare @yymm varchar(6)
declare @emptypename varchar(1000)
declare @emptypetotal varchar(1000)
declare @sql varchar(4000)set @yymm='201208'
select @emptypetotal=isnull(@emptypetotal+'+','')+'['+itemname+']' from syssetlist where typeid='rs01'
select @emptypename=isnull(@emptypename+',','')+'['+itemname+']' from syssetlist where typeid='rs01'
set @sql='select [一级部门],[二级部门],[三级部门],[四级部门],'+@emptypename+',('+@emptypetotal+') as [总计] from
(
select
e.partname as [一级部门],d1.partname as [二级部门],d2.partname as [三级部门],
d3.partname as [四级部门],e.emptypename,count(e.emptypename) as empnum,
from
peremployee e left join perdepart d on e.partno=d.partno
left join perdepart d1 on e.partno=d1.dtid2
left join perdepart d2 on e.partno=d2.dtid3
left join perdepart d3 on e.partno=d3.dtid4
where
exists(select * from perempmonthdoc em where em.empid=e.empid and em.yymm='''+@yymm+''')
and (d.dtid2!='' or d.dtid3!='' or d.dtid4!='')
group by e.partname,d1.partname,d2.partname,d3.partname,d4.partname,e.emptypename
) as S PIVOT(sum(empnum) for emptypename in('+@emptypename+')) as D'
exec(@sql)
最终效果:一级部门 部门 子部门 明细部门 职员 员工 总计
create proc test
( @yymm varchar(6)='201208',@emptypename varchar(1000),@emptypetotal varchar(1000))
as
begin
declare @sql varchar(4000)select @emptypetotal=isnull(@emptypetotal+'+','')+'['+itemname+']' from syssetlist where typeid='rs01'
select @emptypename=isnull(@emptypename+',','')+'['+itemname+']' from syssetlist where typeid='rs01'
set @sql='select [一级部门],[二级部门],[三级部门],[四级部门],'+@emptypename+',('+@emptypetotal+') as [总计] from
(
select
e.partname as [一级部门],d1.partname as [二级部门],d2.partname as [三级部门],
d3.partname as [四级部门],e.emptypename,count(e.emptypename) as empnum,
from
peremployee e left join perdepart d on e.partno=d.partno
left join perdepart d1 on e.partno=d1.dtid2
left join perdepart d2 on e.partno=d2.dtid3
left join perdepart d3 on e.partno=d3.dtid4
where
exists(select * from perempmonthdoc em where em.empid=e.empid and em.yymm='''+@yymm+''')
and (d.dtid2!='' or d.dtid3!='' or d.dtid4!='')
group by e.partname,d1.partname,d2.partname,d3.partname,d4.partname,e.emptypename
) as S PIVOT(sum(empnum) for emptypename in('+@emptypename+')) as D'
exec(@sql)
end
CREATE PROC test(
@yymm VARCHAR(6) = '201208',
@emptypename VARCHAR(1000),
@emptypetotal VARCHAR(1000))
AS
BEGIN
DECLARE
@sql VARCHAR(4000)
SELECT @emptypetotal = Isnull(@emptypetotal + '+','') + '[' + itemname + ']'
FROM syssetlist
WHERE typeid = 'rs01'
SELECT @emptypename = Isnull(@emptypename + ',','') + '[' + itemname + ']'
FROM syssetlist
WHERE typeid = 'rs01'
SET @sql = 'select [一级部门],[二级部门],[三级部门],[四级部门],' + @emptypename + ',(' + @emptypetotal + ') as [总计] from
(
select
e.partname as [一级部门],d1.partname as [二级部门],d2.partname as [三级部门],
d3.partname as [四级部门],e.emptypename,count(e.emptypename) as empnum,
from
peremployee e left join perdepart d on e.partno=d.partno
left join perdepart d1 on e.partno=d1.dtid2
left join perdepart d2 on e.partno=d2.dtid3
left join perdepart d3 on e.partno=d3.dtid4
where
exists(select * from perempmonthdoc em where em.empid=e.empid and em.yymm=''' + @yymm + ''')
and (d.dtid2!='' or d.dtid3!='' or d.dtid4!='')
group by e.partname,d1.partname,d2.partname,d3.partname,d4.partname,e.emptypename
) as S PIVOT(sum(empnum) for emptypename in(' + @emptypename + ')) as D'
EXEC( @sql)
END