谁帮我把下面的SQL语句修改成存储过程，或是新增一个函数？谢谢大家了！！！急用！

[w524106785]

谁帮我把下面的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)




最终效果：

一级部门 部门 子部门 明细部门 职员 员工 临时工 实习生 试用工 正式工 总计

[發糞塗牆]

在括号里面的3个是参数，调用时要传入值的，第一个我帮你填了默认值，使用的时候可以不传，直接计算201208的结果。

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