我用以下方法报错:
fmDM.ADOQuery.Close;
fmDM.ADOQuery.sql.LoadFromFile(E:\AA.sql);
fmDM.ADOQuery.Execute;
出现"GO"错误IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'fyykjhzx' AND type = 'P')
DROP PROCEDURE fyykjhzx
GOcreate proc fyykjhzx
@zjxz varchar(10),--资金性质
@kjnd char(4),--会计年度
@kjqj char(2),--会计期间
@ywcs varchar(8),--业务自室
@dwdm varchar(10),--单位代码
@yskm varchar(10)--预算科目
as
declare
@Yskmttmp varchar(10), --预算科目
@Yskmmc varchar(50), @jhxj float(50),
@jhzjzf float,
@jhsqzf float,
@jhbk float,
@sjxj float,
@sjzjzf float,
@sjsqzf float,
@sjbk float,
@kyzb float,
@bfb float
create table #t
(
yskmdm varchar(10),
yskmmc varchar(60),
jhxj float,
jhzjzf float,
jhsqzf float,
jhbk float,
sjxj float,
sjzjzf float,
sjsqzf float,
sjbk float,
kyzb float,
bfb numeric(4,2)
) if @yskm = '全部'
begin
declare cur_yskm cursor for
select distinct YSKMDM from ZB_YKJHNR where kjqj1 =@kjnd + @kjqj
open cur_yskm FETCH NEXT FROM cur_yskm
INTO @Yskmttmp
WHILE @@FETCH_STATUS = 0
begin
select @Yskmmc = fzmc from GL_fzxzl where fzdm=@Yskmttmp and gsdm=''
select @jhzjzf = isnull(sum(a.JE),0) from ZB_YKJHNR a, ZB_YKJHML b where ltrim(rtrim(a.YSKMDM)) = @Yskmttmp and a.ZFFSDM='01'
and ltrim(rtrim(a.zjxzdm)) =@zjxz and a.kjqj1=@kjnd + @kjqj and ltrim(rtrim(b.DZKDM))=@ywcs and ltrim(rtrim(b.YSDWDM))=@dwdm and a.JHLB=b.jhlb and a.kjnd=b.kjnd
print @jhzjzf
select @jhsqzf = isnull(sum(a.JE),0) from ZB_YKJHNR a, ZB_YKJHML b where ltrim(rtrim(a.YSKMDM)) = @Yskmttmp and a.ZFFSDM='02'
and ltrim(rtrim(a.zjxzdm))=@zjxz and a.kjqj1=@kjnd + @kjqj and ltrim(rtrim(b.DZKDM))=@ywcs and ltrim(rtrim(b.YSDWDM))=@dwdm and a.JHLB=b.jhlb and a.kjnd=b.kjnd
select @jhbk = isnull(sum(a.JE),0) from ZB_YKJHNR a, ZB_YKJHML b where ltrim(rtrim(a.YSKMDM)) = @Yskmttmp and a.ZFFSDM='03'
and ltrim(rtrim(a.zjxzdm)) =@zjxz and a.kjqj1=@kjnd + @kjqj and ltrim(rtrim(b.DZKDM))=@ywcs and ltrim(rtrim(b.YSDWDM))=@dwdm and a.JHLB=b.jhlb and a.kjnd=b.kjnd
select @sjzjzf = isnull(sum(a.ZBJE),0) from ZB_ZFPZNR_Y a, ZB_ZFPZML_Y b where ltrim(rtrim(a.YSKMDM))= @Yskmttmp and a.ZFFSDM='01'
and ltrim(rtrim(a.zjxzdm)) =@zjxz and a.pdqj=@kjnd + @kjqj and ltrim(rtrim(b.DZKDM))=@ywcs and ltrim(rtrim(b.YSDWDM))=@dwdm and a.ZFLB=b.ZFLB and a.PDH=b.PDH
select @sjsqzf=isnull(sum(a.ZBJE),0) from ZB_ZFPZNR_Y a, ZB_ZFPZML_Y b where ltrim(rtrim(a.YSKMDM)) = @Yskmttmp and a.ZFFSDM='02'
and ltrim(rtrim(a.zjxzdm))=@zjxz and a.pdqj=@kjnd + @kjqj and ltrim(rtrim(b.DZKDM))=@ywcs and ltrim(rtrim(b.YSDWDM))=@dwdm and a.ZFLB=b.ZFLB and a.PDH=b.PDH
select @sjbk= isnull(sum(a.ZBJE),0) from ZB_ZFPZNR_Y a, ZB_ZFPZML_Y b where ltrim(rtrim(a.YSKMDM)) = @Yskmttmp and a.ZFFSDM='03'
and ltrim(rtrim(a.zjxzdm)) =@zjxz and a.pdqj=@kjnd + @kjqj and ltrim(rtrim(b.DZKDM))=@ywcs and ltrim(rtrim(b.YSDWDM))=@dwdm and a.ZFLB=b.ZFLB and a.PDH=b.PDH
insert into #t (yskmdm, yskmmc, jhzjzf,jhsqzf,jhbk,sjzjzf,sjsqzf,sjbk)values
(@Yskmttmp, @Yskmmc, @jhzjzf,@jhsqzf,@jhbk, @sjzjzf,@sjsqzf,@sjbk) FETCH NEXT FROM cur_yskm INTO @Yskmttmp
end
close cur_yskm
deallocate cur_yskm
update #t set jhxj = jhzjzf + jhsqzf + jhbk, sjxj = sjzjzf + sjsqzf + sjbk
update #t set kyzb = jhxj-sjxj, bfb =( case jhxj when 0 then 0 else sjxj/jhxj *100 end )
select @jhxj= isnull(sum(jhxj),0),@jhzjzf=isnull(sum(jhzjzf),0),@jhsqzf=isnull(sum(jhsqzf),0),@jhbk=isnull(sum(jhbk),0),
@sjxj=isnull(sum(sjxj),0) ,@sjzjzf=isnull(sum(sjzjzf),0),@sjsqzf=isnull(sum(sjsqzf),0),@sjbk=isnull(sum(sjbk),0),
@kyzb = isnull(sum(kyzb),0) from #t insert into #t(yskmmc, jhxj, jhzjzf,jhsqzf,jhbk,sjxj,sjzjzf,sjsqzf,sjbk, kyzb) values
('总计',@jhxj, @jhzjzf,@jhsqzf,@jhbk,@sjxj,@sjzjzf,@sjsqzf,@sjbk,@kyzb)
update #t set bfb = ( case jhxj when 0 then 0 else sjxj/jhxj * 100 end ) where yskmmc = '总计'
end else
begin
select @Yskmmc = fzmc from GL_fzxzl where fzdm=@yskm and gsdm=''
select @jhzjzf = isnull(sum(a.JE),0) from ZB_YKJHNR a, ZB_YKJHML b where a.YSKMDM = @yskm and a.ZFFSDM='01'
and a.zjxzdm =@zjxz and a.kjqj1=@kjnd + @kjqj and b.DZKDM=@ywcs and b.YSDWDM=@dwdm and a.JHLB=b.jhlb and a.kjnd=b.kjnd
select @jhsqzf = isnull(sum(a.JE),0) from ZB_YKJHNR a, ZB_YKJHML b where a.YSKMDM = @yskm and a.ZFFSDM='02'
and a.zjxzdm =@zjxz and a.kjqj1=@kjnd + @kjqj and b.DZKDM=@ywcs and b.YSDWDM=@dwdm and a.JHLB=b.jhlb and a.kjnd=b.kjnd
select @jhbk = isnull(sum(a.JE),0) from ZB_YKJHNR a, ZB_YKJHML b where a.YSKMDM = @yskm and a.ZFFSDM='03'
and a.zjxzdm =@zjxz and a.kjqj1=@kjnd + @kjqj and b.DZKDM=@ywcs and b.YSDWDM=@dwdm and a.JHLB=b.jhlb and a.kjnd=b.kjnd
select @sjzjzf = isnull(sum(a.JE),0) from ZB_ZFPZNR_Y a, ZB_ZFPZML_Y b where a.YSKMDM = @yskm and a.ZFFSDM='01'
and a.zjxzdm =@zjxz and a.pdqj=@kjnd + @kjqj and b.DZKDM=@ywcs and b.YSDWDM=@dwdm and a.ZFLB=b.ZFLB and a.PDH=b.PDH
select @sjsqzf= isnull(sum(a.JE),0) from ZB_ZFPZNR_Y a, ZB_ZFPZML_Y b where a.YSKMDM = @yskm and a.ZFFSDM='02'
and a.zjxzdm =@zjxz and a.pdqj=@kjnd + @kjqj and b.DZKDM=@ywcs and b.YSDWDM=@dwdm and a.ZFLB=b.ZFLB and a.PDH=b.PDH
select @sjbk= isnull(sum(a.JE),0) from ZB_ZFPZNR_Y a, ZB_ZFPZML_Y b where a.YSKMDM = @yskm and a.ZFFSDM='03'
and a.zjxzdm =@zjxz and a.pdqj=@kjnd + @kjqj and b.DZKDM=@ywcs and b.YSDWDM=@dwdm and a.ZFLB=b.ZFLB and a.PDH=b.PDH
insert into #t (yskmdm, yskmmc, jhzjzf,jhsqzf,jhbk,sjzjzf,sjsqzf,sjbk)values
(@yskm, @Yskmmc, @jhzjzf,@jhsqzf,@jhbk, @sjzjzf,@sjsqzf,@sjbk)
update #t set kyzb = jhxj-sjxj, bfb =( case jhxj when 0 then 0 else sjxj/jhxj *100 end )
end
select * from #t
go
谢谢
fmDM.ADOQuery.Close;
fmDM.ADOQuery.sql.LoadFromFile(E:\AA.sql);
fmDM.ADOQuery.Execute;
出现"GO"错误IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'fyykjhzx' AND type = 'P')
DROP PROCEDURE fyykjhzx
GOcreate proc fyykjhzx
@zjxz varchar(10),--资金性质
@kjnd char(4),--会计年度
@kjqj char(2),--会计期间
@ywcs varchar(8),--业务自室
@dwdm varchar(10),--单位代码
@yskm varchar(10)--预算科目
as
declare
@Yskmttmp varchar(10), --预算科目
@Yskmmc varchar(50), @jhxj float(50),
@jhzjzf float,
@jhsqzf float,
@jhbk float,
@sjxj float,
@sjzjzf float,
@sjsqzf float,
@sjbk float,
@kyzb float,
@bfb float
create table #t
(
yskmdm varchar(10),
yskmmc varchar(60),
jhxj float,
jhzjzf float,
jhsqzf float,
jhbk float,
sjxj float,
sjzjzf float,
sjsqzf float,
sjbk float,
kyzb float,
bfb numeric(4,2)
) if @yskm = '全部'
begin
declare cur_yskm cursor for
select distinct YSKMDM from ZB_YKJHNR where kjqj1 =@kjnd + @kjqj
open cur_yskm FETCH NEXT FROM cur_yskm
INTO @Yskmttmp
WHILE @@FETCH_STATUS = 0
begin
select @Yskmmc = fzmc from GL_fzxzl where fzdm=@Yskmttmp and gsdm=''
select @jhzjzf = isnull(sum(a.JE),0) from ZB_YKJHNR a, ZB_YKJHML b where ltrim(rtrim(a.YSKMDM)) = @Yskmttmp and a.ZFFSDM='01'
and ltrim(rtrim(a.zjxzdm)) =@zjxz and a.kjqj1=@kjnd + @kjqj and ltrim(rtrim(b.DZKDM))=@ywcs and ltrim(rtrim(b.YSDWDM))=@dwdm and a.JHLB=b.jhlb and a.kjnd=b.kjnd
print @jhzjzf
select @jhsqzf = isnull(sum(a.JE),0) from ZB_YKJHNR a, ZB_YKJHML b where ltrim(rtrim(a.YSKMDM)) = @Yskmttmp and a.ZFFSDM='02'
and ltrim(rtrim(a.zjxzdm))=@zjxz and a.kjqj1=@kjnd + @kjqj and ltrim(rtrim(b.DZKDM))=@ywcs and ltrim(rtrim(b.YSDWDM))=@dwdm and a.JHLB=b.jhlb and a.kjnd=b.kjnd
select @jhbk = isnull(sum(a.JE),0) from ZB_YKJHNR a, ZB_YKJHML b where ltrim(rtrim(a.YSKMDM)) = @Yskmttmp and a.ZFFSDM='03'
and ltrim(rtrim(a.zjxzdm)) =@zjxz and a.kjqj1=@kjnd + @kjqj and ltrim(rtrim(b.DZKDM))=@ywcs and ltrim(rtrim(b.YSDWDM))=@dwdm and a.JHLB=b.jhlb and a.kjnd=b.kjnd
select @sjzjzf = isnull(sum(a.ZBJE),0) from ZB_ZFPZNR_Y a, ZB_ZFPZML_Y b where ltrim(rtrim(a.YSKMDM))= @Yskmttmp and a.ZFFSDM='01'
and ltrim(rtrim(a.zjxzdm)) =@zjxz and a.pdqj=@kjnd + @kjqj and ltrim(rtrim(b.DZKDM))=@ywcs and ltrim(rtrim(b.YSDWDM))=@dwdm and a.ZFLB=b.ZFLB and a.PDH=b.PDH
select @sjsqzf=isnull(sum(a.ZBJE),0) from ZB_ZFPZNR_Y a, ZB_ZFPZML_Y b where ltrim(rtrim(a.YSKMDM)) = @Yskmttmp and a.ZFFSDM='02'
and ltrim(rtrim(a.zjxzdm))=@zjxz and a.pdqj=@kjnd + @kjqj and ltrim(rtrim(b.DZKDM))=@ywcs and ltrim(rtrim(b.YSDWDM))=@dwdm and a.ZFLB=b.ZFLB and a.PDH=b.PDH
select @sjbk= isnull(sum(a.ZBJE),0) from ZB_ZFPZNR_Y a, ZB_ZFPZML_Y b where ltrim(rtrim(a.YSKMDM)) = @Yskmttmp and a.ZFFSDM='03'
and ltrim(rtrim(a.zjxzdm)) =@zjxz and a.pdqj=@kjnd + @kjqj and ltrim(rtrim(b.DZKDM))=@ywcs and ltrim(rtrim(b.YSDWDM))=@dwdm and a.ZFLB=b.ZFLB and a.PDH=b.PDH
insert into #t (yskmdm, yskmmc, jhzjzf,jhsqzf,jhbk,sjzjzf,sjsqzf,sjbk)values
(@Yskmttmp, @Yskmmc, @jhzjzf,@jhsqzf,@jhbk, @sjzjzf,@sjsqzf,@sjbk) FETCH NEXT FROM cur_yskm INTO @Yskmttmp
end
close cur_yskm
deallocate cur_yskm
update #t set jhxj = jhzjzf + jhsqzf + jhbk, sjxj = sjzjzf + sjsqzf + sjbk
update #t set kyzb = jhxj-sjxj, bfb =( case jhxj when 0 then 0 else sjxj/jhxj *100 end )
select @jhxj= isnull(sum(jhxj),0),@jhzjzf=isnull(sum(jhzjzf),0),@jhsqzf=isnull(sum(jhsqzf),0),@jhbk=isnull(sum(jhbk),0),
@sjxj=isnull(sum(sjxj),0) ,@sjzjzf=isnull(sum(sjzjzf),0),@sjsqzf=isnull(sum(sjsqzf),0),@sjbk=isnull(sum(sjbk),0),
@kyzb = isnull(sum(kyzb),0) from #t insert into #t(yskmmc, jhxj, jhzjzf,jhsqzf,jhbk,sjxj,sjzjzf,sjsqzf,sjbk, kyzb) values
('总计',@jhxj, @jhzjzf,@jhsqzf,@jhbk,@sjxj,@sjzjzf,@sjsqzf,@sjbk,@kyzb)
update #t set bfb = ( case jhxj when 0 then 0 else sjxj/jhxj * 100 end ) where yskmmc = '总计'
end else
begin
select @Yskmmc = fzmc from GL_fzxzl where fzdm=@yskm and gsdm=''
select @jhzjzf = isnull(sum(a.JE),0) from ZB_YKJHNR a, ZB_YKJHML b where a.YSKMDM = @yskm and a.ZFFSDM='01'
and a.zjxzdm =@zjxz and a.kjqj1=@kjnd + @kjqj and b.DZKDM=@ywcs and b.YSDWDM=@dwdm and a.JHLB=b.jhlb and a.kjnd=b.kjnd
select @jhsqzf = isnull(sum(a.JE),0) from ZB_YKJHNR a, ZB_YKJHML b where a.YSKMDM = @yskm and a.ZFFSDM='02'
and a.zjxzdm =@zjxz and a.kjqj1=@kjnd + @kjqj and b.DZKDM=@ywcs and b.YSDWDM=@dwdm and a.JHLB=b.jhlb and a.kjnd=b.kjnd
select @jhbk = isnull(sum(a.JE),0) from ZB_YKJHNR a, ZB_YKJHML b where a.YSKMDM = @yskm and a.ZFFSDM='03'
and a.zjxzdm =@zjxz and a.kjqj1=@kjnd + @kjqj and b.DZKDM=@ywcs and b.YSDWDM=@dwdm and a.JHLB=b.jhlb and a.kjnd=b.kjnd
select @sjzjzf = isnull(sum(a.JE),0) from ZB_ZFPZNR_Y a, ZB_ZFPZML_Y b where a.YSKMDM = @yskm and a.ZFFSDM='01'
and a.zjxzdm =@zjxz and a.pdqj=@kjnd + @kjqj and b.DZKDM=@ywcs and b.YSDWDM=@dwdm and a.ZFLB=b.ZFLB and a.PDH=b.PDH
select @sjsqzf= isnull(sum(a.JE),0) from ZB_ZFPZNR_Y a, ZB_ZFPZML_Y b where a.YSKMDM = @yskm and a.ZFFSDM='02'
and a.zjxzdm =@zjxz and a.pdqj=@kjnd + @kjqj and b.DZKDM=@ywcs and b.YSDWDM=@dwdm and a.ZFLB=b.ZFLB and a.PDH=b.PDH
select @sjbk= isnull(sum(a.JE),0) from ZB_ZFPZNR_Y a, ZB_ZFPZML_Y b where a.YSKMDM = @yskm and a.ZFFSDM='03'
and a.zjxzdm =@zjxz and a.pdqj=@kjnd + @kjqj and b.DZKDM=@ywcs and b.YSDWDM=@dwdm and a.ZFLB=b.ZFLB and a.PDH=b.PDH
insert into #t (yskmdm, yskmmc, jhzjzf,jhsqzf,jhbk,sjzjzf,sjsqzf,sjbk)values
(@yskm, @Yskmmc, @jhzjzf,@jhsqzf,@jhbk, @sjzjzf,@sjsqzf,@sjbk)
update #t set kyzb = jhxj-sjxj, bfb =( case jhxj when 0 then 0 else sjxj/jhxj *100 end )
end
select * from #t
go
谢谢
像在查询分析器里一样执行脚本!!!
下面是我处理*.sql文件的一段代码
Var
AFile : TextFile;
I : Integer;
tmp_str : widestring;
tmp : string;
Begin
AssignFile(AFile,FileName);
Reset(AFile);
While Not Eof(AFile) do
begin
Readln(AFile,tmp_str);
If (Pos('/*',tmp_str) <= 0 ) and (Pos('*/',tmp_str) <= 0 ) then
tmp := tmp + tmp_str;
If Pos('GO',tmp) > 0 then
begin
StrL.Add(DelCtrlChar(tmp,'GO'));
tmp := '';
end;
end; // end while
CloseFile(AFile);
Result := True; end;