DECLARE authors_cursor CURSOR FOR
SELECT au_id, au_fname, au_lname
FROM authors
WHERE state = "UT"
ORDER BY au_idOPEN authors_cursorFETCH NEXT FROM authors_cursor
INTO @au_id, @au_fname, @au_lnameWHILE @@FETCH_STATUS = 0
BEGIN
.....
END
SELECT au_id, au_fname, au_lname
FROM authors
WHERE state = "UT"
ORDER BY au_idOPEN authors_cursorFETCH NEXT FROM authors_cursor
INTO @au_id, @au_fname, @au_lnameWHILE @@FETCH_STATUS = 0
BEGIN
.....
END
select DISTINCT a.'+@tabletype+'_no,a.'+@tabletype+'_ed_no,a.'+@tabletype+'_month from gm_tj2002..'+@ed_type+@tabletype+@s_no+' as a
inner join gm_pub..pub_ed on gm_pub..pub_ed.ed_no=a.'+@tabletype+'_ed_no where a.'+@tabletype+'_month='''+'13''')open uc_qryData其中,@开头的都是变量,是动态的
@tablepro varchar(3), --年报
@tabletype varchar(10), --efy,eft
@ed_type char(1) , --'p'工业,'s'商业
@rpt_status varchar(6) --1 0 0 0 1 0
--录入员 领导 企业 市地 省份 国家
as
declare @tabno varchar(3),--表号
@edno varchar(20),--企业代码
@tabmon varchar (2),--报表月份
@opman1 varchar(20),--操作员1
@opman2 varchar(20),--操作员2
@opman3 varchar(20), --操作员3
@s_no char(2), --省号
@n int --循环变量set @n=0
set @opman1 ='sa'
set @opman2='sa'
set @opman3='sa'while(@n<31)
beginset @n=@n+1
if (@n<10)
set @s_no='0'+cast(@n as char(2))
else
set @s_no=cast(@n as char(2))exec('declare uc_qryData cursor for
select DISTINCT a.'+@tabletype+'_no,a.'+@tabletype+'_ed_no,a.'+@tabletype+'_month from gm_tj2002..'+@ed_type+@tabletype+@s_no+' as a
inner join gm_pub..pub_ed on gm_pub..pub_ed.ed_no=a.'+@tabletype+'_ed_no where a.'+@tabletype+'_month='''+'13''')exec('delete from gm_tj2002..DAT_RPTSTATUS where DAT_RPTSTATUS.rptst_type='''+@tabletype+''' and DAT_RPTSTATUS.rptst_ed_no like '''+@ed_type+@s_no+'%''')
open uc_qryData
fetch next from uc_qryData into @tabno,@edno,@tabmon
while(@@FETCH_STATUS<>-1)
begin
--若为商业企业的财务表,则表号不为'002'就全置为'001'
if (@tabletype ='EFT' and @ed_type='s' and @tabno<>'002')
begin
set @tabno='001'
end
insert into gm_tj2002..DAT_RPTSTATUS (DAT_RPTSTATUS.rptst_no,
DAT_RPTSTATUS.rptst_ed_no,DAT_RPTSTATUS.rptst_pro,
DAT_RPTSTATUS.rptst_type,DAT_RPTSTATUS.rptst_month,
DAT_RPTSTATUS.rptst_status,DAT_RPTSTATUS.rptst_man1,
DAT_RPTSTATUS.rptst_man2,DAT_RPTSTATUS.rptst_man3,
DAT_RPTSTATUS.RPTST_OPTIME1,DAT_RPTSTATUS.RPTST_OPTIME2,
DAT_RPTSTATUS.RPTST_OPTIME3,DAT_RPTSTATUS.RPTST_OPTIME4)
values
(@tabno,@edno,@tablepro,@tabletype,@tabmon,@rpt_status,@opman1,@opman2,@opman3,'2003-3-20','2003-3-20','2003-3-20','2003-3-20')fetch next from uc_qryData into @tabno,@edno,@tabmon
endclose uc_qryData
deallocate uc_qryData
end