括符是半角,obj_id是字符型字段!
問題不是出在這里!
我不妨將整個腳本貼出來,大家看看!
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
----------------------*******************************----------------------------------
--部門費用匯總表
--Script Build by LiuXueJun 2005.10.10
----------------------*******************************----------------------------------
ALTER procedure sp_feebydep
@p_com_id char(4), --公司代號
@p_dep_id char(8), --部門代號
@p_spe_id char(8), --專案代號
@yy char(2), --年份
@mms char(2), --開始月份
@mme char(2), --截止月份
@obj_ids char(4), --開始科目
@obj_ide char(4), --截止科目
@obj_1_ids char(8), --開始子科目
@obj_1_ide char(8) --截止子科目
as
begin
declare @script nvarchar(1000) --建立臨時表用
declare @script_2 nvarchar(1000) --臨時表中文列名顯示
declare @dep_id varchar(8) --部門代號
declare @dep_name varchar(20) --部門名稱
declare @obj_id varchar(4) --科目代號
declare @dc varchar(1) --借、貸
declare @amount decimal(26,6) --借貨額
declare @h_total decimal(26,6) --水平匯總
declare @v_total decimal(26,6) --垂直匯總
declare @sql nvarchar(255) --
declare @param nvarchar(100) --
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FEEBYDEP]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
exec('DROP TABLE FEEBYDEP')
--根據系統部門資料動態創建表的各列
set @script ='CREATE TABLE FEEBYDEP (lngid INT NOT NULL IDENTITY (1, 1),obj_id VARCHAR(4) NOT NULL ,'
set @script_2 = 'SELECT obj_id AS 科目_部門,'
declare cur_dep cursor scroll for select ltrim(rtrim(dep_id)),ltrim(rtrim(dep_name)) from DEP order by dep_id
open cur_dep
fetch next from cur_dep into @dep_id,@dep_name
while @@fetch_status>=0
begin
set @script=@script + '['+@dep_id+'] DECIMAL(26,6) NULL,'
set @script_2 = @script_2 + '['+@dep_id+'] as '+ rtrim(ltrim(@dep_name))+','
fetch next from cur_dep into @dep_id,@dep_name
end
if right(@script,1)=','
set @script = @script + 'total DECIMAL(26,6) NULL'
set @script=@script + ')'
exec(@script)
set @script_2 = @script_2 + ' total AS 合計 FROM FEEBYDEP'
--插入明細科目(每科目為一行)
declare cur_obj cursor for select distinct b.obj_id from VOU1_M a,VOU1_D b
where a.vou1_m_id=b.vou1_m_id --and a.com_id like @p_com_id+'%' and b.dep_id like @p_dep_id+'%' and b.spe_id like @p_spe_id+'%'
--and a.yy=@yy and a.mm>=@mms and a.mm<=@mme and b.obj_id>=@obj_ids and b.obj_id<=@obj_ide and b.obj_1_id>=@obj_1_ids and b.obj_1_id<=@obj_1_ide
order by b.obj_id
open cur_obj
fetch next from cur_obj into @obj_id
while @@Fetch_Status>=0
begin
insert into FEEBYDEP(obj_id) values (@obj_id)
fetch next from cur_obj into @obj_id
end
close cur_obj
deallocate cur_obj
insert into FEEBYDEP(obj_id) values('合計')
--明細科目賦值
declare cur_vou1 cursor for select ltrim(rtrim(b.obj_id)),ltrim(rtrim(b.dep_id)),b.dc,isnull(b.amount_d,0)+isnull(b.amount_c,0) amount from VOU1_M a,VOU1_D b
where a.vou1_m_id=b.vou1_m_id --and a.com_id like @p_com_id+'%' and b.dep_id like @p_dep_id+'%' and b.spe_id like @p_spe_id+'%'
--and a.yy=@yy and a.mm>=@mms and a.mm<=@mme and b.obj_id>=@obj_ids and b.obj_id<=@obj_ide and b.obj_1_id>=@obj_1_ids and b.obj_1_id<=@obj_1_ide
order by b.obj_id
open cur_vou1
fetch next from cur_vou1 into @obj_id,@dep_id,@dc,@amount
while @@fetch_status>=0
begin
select @sql='UPDATE FEEBYDEP SET ['+@dep_id+'] = ISNULL( ['+@dep_id+'] ,0) + ' + cast(@amount as varchar(30)) +' WHERE obj_id=''' + @obj_id+''''
print @sql
exec @sql
fetch next from cur_vou1 into @obj_id,@dep_id,@dc,@amount
end
close cur_vou1
deallocate cur_vou1
--先計算橫向匯總值
select @sql = ''
fetch first from cur_dep into @dep_id,@dep_name
while @@fetch_status>=0
begin
set @sql = @sql +'ISNULL(['+@dep_id+'],0)+'
fetch next from cur_dep into @dep_id,@dep_name
end
select @sql=substring(@sql,1,len(@sql)-1)
select @sql = N'UPDATE FEEBYDEP SET total='+@sql
declare cur_feebydep cursor for select obj_id from FEEBYDEP order by lngid
open cur_feebydep
fetch next from cur_feebydep into @obj_id
while @@fetch_status>=0
begin
select @script=@sql+' WHERE obj_id='''+@obj_id+''''
print '@script='+@script
print @script
exec @script
fetch next from cur_feebydep into @obj_id
end
close cur_feebydep
deallocate cur_feebydep
print 'ok'
--再計算縱向匯總值
select @param =N'@total DECIMAL(26,6) OUTPUT'
fetch first from cur_dep into @dep_id,@dep_name
while @@fetch_status>=0
begin
select @sql= N'SELECT @total=SUM(ISNULL(['+@dep_id+'],0)) FROM FEEBYDEP'
exec sp_executesql @sql,@param,@v_total output
select @v_total=isnull(@v_total,0)
print '@v_total='+cast(@v_total as varchar(30))
print '@dep_id='+@dep_id
if @v_total<>0
begin
select @sql= 'UPDATE FEEBYDEP SET ['+@dep_id+']='+cast(@v_total as varchar(30))+' WHERE obj_id=''合計'''
exec @sql
end
fetch next from cur_dep into @dep_id,@dep_name
end
select @v_total=sum(isnull(total,0)) from FEEBYDEP
select @v_total= isnull(@v_total,0)
if @v_total<>0
begin
select @sql= 'UPDATE FEEBYDEP SET total='+cast(@v_total as varchar(30))+' WHERE obj_id=''合計'''
exec @sql
end
close cur_dep
deallocate cur_dep
print @script_2
exec(@script_2)
exec('DROP TABLE FEEBYDEP')
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO