有个存储过程CREATE PROCEDURE BakJBXX
@tab sysname,
@date1 varchar(10),
@date2 varchar(10)
AS
declare @check_name varchar(20)
declare curJBXX cursor for select table_name from XXFL where FL_name = '基本信息' and Flag_name =''+@check_name+''
Begin Tran Search
open curJBXX fetch next from curJBXX into @tab
while @@fetch_status = 0
begin
exec('
begin
insert zaochuan_BAK.dbo.'+ @tab +' select * from aaa.dbo.'+ @tab +' where '+ @check_name +' >= '''+ @date1 +''' and '+ @check_name + ' <= '''+@date2+'''
delete '+@tab+' where '+ @check_name + ' >= '''+@date1+''' and' + @check_name + ' <= ''' +@date2 + '''
end
')
fetch next from curJBXX into @tab
end
close curJBXX
deallocate curJBXX
if @@Error<>0
begin
Rollback tran Search
return(1)
end
Commit Tran Search
GO我执行存储过程的时候 exec BakJBXX @date1='2005-6-12', @date2='2005-6-13',@check_name ='Y'为什么没有效果出来阿? 是哪里错了
@tab sysname,
@date1 varchar(10),
@date2 varchar(10)
AS
declare @check_name varchar(20)
declare curJBXX cursor for select table_name from XXFL where FL_name = '基本信息' and Flag_name =''+@check_name+''
Begin Tran Search
open curJBXX fetch next from curJBXX into @tab
while @@fetch_status = 0
begin
exec('
begin
insert zaochuan_BAK.dbo.'+ @tab +' select * from aaa.dbo.'+ @tab +' where '+ @check_name +' >= '''+ @date1 +''' and '+ @check_name + ' <= '''+@date2+'''
delete '+@tab+' where '+ @check_name + ' >= '''+@date1+''' and' + @check_name + ' <= ''' +@date2 + '''
end
')
fetch next from curJBXX into @tab
end
close curJBXX
deallocate curJBXX
if @@Error<>0
begin
Rollback tran Search
return(1)
end
Commit Tran Search
GO我执行存储过程的时候 exec BakJBXX @date1='2005-6-12', @date2='2005-6-13',@check_name ='Y'为什么没有效果出来阿? 是哪里错了
exec BakJBXX @tab='y', @date1='2005-6-12', @date2='2005-6-13'
是这的问题嘛
declare curJBXX cursor for select table_name from XXFL where FL_name = '基本信息' and Flag_name =''''+@check_name+'''' --这个地方错了吧
declare @check_name varchar(20)
declare curJBXX cursor for select table_name from XXFL where FL_name = '基本信息' and Flag_name =''+@check_name+'' fetch next from curJBXX into @tab-------------------
改成:
declare @check_name varchar(20)
declare curJBXX cursor for select table_name from XXFL where FL_name = '基本信息' and Flag_name =@tab fetch next from curJBXX into @check_name
declare curJBXX cursor for select table_name from XXFL where FL_name = '基本信息' and Flag_name =''''+@check_name+''''