求解一存储过程!分不够再加!UP有分!急...在线等.....

openhouse 2005-11-16 02:20:40

if exists (select * from sysobjects where id = object_id(N'[dbo].[CheckBD_P]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure CheckBD_P
GO

create proc CheckBD_P
@TableName char(100)
as
create table #tmp
(employeename char(20))

declare @sqlemp varchar(500)

set @sqlemp ='
insert into #tmp select * from
(
select distinct jbr from '+ @TableName + '
union
select distinct shr from '+ @TableName + '
union
select distinct zgr from '+ @TableName + '
) as a'

exec (@sqlemp)

create table #tmp1
(
employeename char(20),
jbrsfpzzs int ,
jbrsumje decimal(16,2),
jbrbz decimal(16,2),
shrsfpzzs int ,
shrsumje decimal(16,2),
shrjbrbz decimal(16,2),
zgrsfpzzs int ,
zgrsumje decimal(16,2),
zgrjbrbz decimal(16,2)
)

declare @name char(20) --当前凭证中所有的人员名称

declare @sumje decimal(16,2) --当前凭证表中借方金额的总和

--@sumjbr制表人总金额,@sumshr复核人总金额,@sumzgr记账人总金额
declare @sumjbr decimal(16,2) , @sumshr decimal(16,2) ,@sumzgr decimal(16,2)

--@sumjbrpzzs制表人笔数,@sumshrpzzs复核人笔数,@sumzgrpzzs记账人笔数
declare @sumjbrpzzs int ,@sumshrpzzs int,@sumzgrpzzs int

--@sumjejbr制表人比重,@sumjeshr复核人比重,@sumjezgr记账人比重
declare @sumjejbr decimal(16,2),@sumjeshr decimal(16,2) ,@sumjezgr decimal(16,2)

declare @sqlje varchar(500)

set @sqlje='declare @sumje decimal(16,2) select @sumje = sum(jfje) from '+ @TableName

exec (@sqlje)

declare @sql varchar(1000)

declare cur cursor for
select * from #tmp
open cur
fetch next from cur into @name

while @@FETCH_STATUS = 0
begin
set @sql = '
select @sumjbrpzzs=sum(sfpzzs),@sumjbr=sum(jfje),@sumjejbr=(@sumjbr/@sumje)*100
from '+ @TableName + '
where jbr=@name

select @sumshrpzzs=sum(sfpzzs),@sumshr=sum(jfje),@sumjeshr=(@sumshr/@sumje)*100
from '+ @TableName + '
where shr = @name

select @sumzgrpzzs=sum(sfpzzs),@sumzgr=sum(jfje),@sumjezgr=(@sumzgr/@sumje)*100
from '+ @TableName + '
where zgr = @name
insert into #tmp1 values(@name,@sumjbrpzzs,@sumjbr,@sumjejbr,
@sumshrpzzs,@sumshr,@sumjeshr,@sumzgrpzzs,@sumzgr,@sumjezgr)'
print @sql
exec (@sql)
set @sql =''
fetch next from cur into @name
end
select employeename as '人员名称',
jbrsfpzzs as '制表人笔数' ,
jbrsumje as '制表人金额',
jbrbz as '制表人比重(%)',
shrsfpzzs as '复核人笔数' ,
shrsumje as '复核人金额',
shrjbrbz as '复核人比重(%)',
zgrsfpzzs as '记账人笔数' ,
zgrsumje as '记账人金额',
zgrjbrbz as '记账人比重(%)' from #tmp1
close cur
deallocate cur
drop table #tmp
drop table #tmp1

出错消息:
服务器: 消息 137，级别 15，状态 1，行 2
必须声明变量 '@sumjbrpzzs'。
服务器: 消息 137，级别 15，状态 1，行 6
必须声明变量 '@sumshrpzzs'。
服务器: 消息 137，级别 15，状态 1，行 10
必须声明变量 '@sumzgrpzzs'。
服务器: 消息 137，级别 15，状态 1，行 13
必须声明变量 '@name'。
.............等!

...全文