alter proc digui(@el_no varchar(20),@tab_name varchar(20))
as
begin
--declare @el_no varchar(20)
declare @uid numeric(18,0)
declare @sql varchar(2000)
declare @el_no1 varchar(20)
set @uid=0
set @uid=(select top 1 uid from iebo00d1 where bo_no=@el_no)
if @uid<>0
begin
declare el_no_cur cursor
for select el_no from iebo00d1 where bo_no=@el_no
for read only
open el_no_cur
fetch next from el_no_cur into @el_no1
while @@fetch_status=0
begin
set @sql='insert into ' +@tab_name+ '(el_no) select el_no from iebo00d1 where bo_no='''+@el_no +''''
exec (@sql)
exec digui @el_no1,@tab_name
fetch next from el_no_cur into @el_no1
end
close el_no_cur
deallocate el_no_cur
執行:delete from #temp_elno
exec digui '0-0074-001-1','#temp_elno'
出錯:
Server: Msg 16915, Level 16, State 1, Procedure digui, Line 13
A cursor with the name 'el_no_cur' already exists.
Server: Msg 16905, Level 16, State 1, Procedure digui, Line 16
The cursor is already open.
這樣怎樣改進?
as
begin
--declare @el_no varchar(20)
declare @uid numeric(18,0)
declare @sql varchar(2000)
declare @el_no1 varchar(20)
set @uid=0
set @uid=(select top 1 uid from iebo00d1 where bo_no=@el_no)
if @uid<>0
begin
declare el_no_cur cursor
for select el_no from iebo00d1 where bo_no=@el_no
for read only
open el_no_cur
fetch next from el_no_cur into @el_no1
while @@fetch_status=0
begin
set @sql='insert into ' +@tab_name+ '(el_no) select el_no from iebo00d1 where bo_no='''+@el_no +''''
exec (@sql)
exec digui @el_no1,@tab_name
fetch next from el_no_cur into @el_no1
end
close el_no_cur
deallocate el_no_cur
執行:delete from #temp_elno
exec digui '0-0074-001-1','#temp_elno'
出錯:
Server: Msg 16915, Level 16, State 1, Procedure digui, Line 13
A cursor with the name 'el_no_cur' already exists.
Server: Msg 16905, Level 16, State 1, Procedure digui, Line 16
The cursor is already open.
這樣怎樣改進?
deallocate el_no_cur
改放在end 上面
要么就用动态语句 exec(@sql)之类的。
alter proc digui(@el_no varchar(20),@tab_name varchar(20))
as
begin
--declare @el_no varchar(20)
declare @uid numeric(18,0)
declare @sql varchar(2000)
declare @el_no1 varchar(20)
declare @id int
declare @count int
set @uid=0
set @uid=(select top 1 uid from iebo00d1 where bo_no=@el_no)
if @uid<>0 begin
select id=identity(int,1,1),el_no into #aa from iebo00d1
set @count=@@rowcount
while @id<@count
begin
select @el_no1=el_no from #aa where id=@count
set @sql='insert into ' +@tab_name+ '(el_no) select el_no from iebo00d1 where bo_no='''+@el_no +''''
exec (@sql)
exec digui @el_no1,@tab_name
set @id=@id+1
endend
set @sql='insert into ' +@tab_name+ '(el_no) select el_no from iebo00d1 where bo_no='''+@el_no +''''
--->>
select @el_no1=el_no from #aa where id=@id
set @sql='insert into ' +@tab_name+ '(el_no) select el_no from iebo00d1 where bo_no='''+@el_no +''''
-->
select id=identity(int,1,1),el_no into #aa from iebo00d1 where bo_no=@el_no
--试一试,不知道行不行,临时表#aa也多次被创建,估计也会有问题!