--1 A处我想增加一个条件语句,即:IF OBJECT_ID('''R'+'+@po+''') IS NOT NULL
但是这条语句没有作用if object_id('A') is not null
drop table A
create table A(ygbh varchar(8),wlk varchar(8),sbk varchar(8),wlstep varchar(3),po varchar(7))
insert into A select a.ygbh,a.wlk,a.sbk,b.wlstep,'' from wpsjxx as a left outer join wpbnloc as b on a.sbk=b.wlreader where zt=7
update A set po=(select widpo from wpidcard where widno=A.WLK)
declare R_PO cursor
static
for select po,ygbh,wlk,wlstep from A
declare
@po varchar(7),
@ygbh varchar(8),
@wlk varchar(8),
@wlstep varchar(3), @sql varchar(8000)
open R_PO
fetch next from R_PO into @po,@ygbh,@wlk,@wlstep
while (@@fetch_status=0)
begin
IF OBJECT_ID('''R'+@po+'''') IS NOT NUL
begin
select @sql= ' update ' + 'R'+@po +' set tkm5eno = '+@ygbh+' where tkm5id = '+@wlk+' and tkm5step = '+@wlstep+' '
exec(@sql)
end
fetch next from R_PO into @po,@ygbh,@wlk,@wlstep
end
close R_PO
deallocate R_PO
--2.此游标结合存储过程如何使用,并且每10分钟执行1次,如何实现
将以上代码写在存储存储过程中,然后定义一个作业,每10分钟执行一次--定义创建作业
DECLARE @jobid uniqueidentifier
EXEC msdb.dbo.sp_add_job
@job_name = N'作业名称',
@job_id = @jobid OUTPUT--定义作业步骤
DECLARE @sql nvarchar(400),@dbname sysname
SELECT @dbname=DB_NAME(), --作业步骤在当前数据库中执行
@sql=N'--作业步骤内容' --一般定义的是使用TSQL处理的作业,这里定义要执行的Transact-SQL语句
EXEC msdb.dbo.sp_add_jobstep
@job_id = @jobid,
@step_name = N'作业步骤名称',
@subsystem = 'TSQL', --步骤的类型,一般为TSQL
@database_name=@dbname,
@command = @sql--创建调度
EXEC msdb..sp_add_jobschedule
@job_id = @jobid,
@name = N'调度名称',
@freq_type=4, --每天
@freq_interval=1, --指定每多少天发生一次,这里是1天.
@freq_subday_type=0x8, --重复方式,0x1=在指定的时间,0x4=多少分钟,0x8=多少小时执行一次
@freq_subday_interval=1, --重复周期数,这里每小时执行一次
@active_start_date = NULL, --作业执行的开始日期,为NULL时表示当前日期,格式为YYYYMMDD
@active_end_date = 99991231, --作业执行的停止日期,默认为99991231,格式为YYYYMMDD
@active_start_time = 00000, --作业执行的开始时间,格式为HHMMSS
@active_end_time = 235959 --作业执行的停止时间,格式为HHMMSS
2. job
3. trigger 里 exec usp
drop table A
create table A(ygbh varchar(8),wlk varchar(8),sbk varchar(8),wlstep varchar(3),po varchar(7))
insert into A select a.ygbh,a.wlk,a.sbk,b.wlstep,'' from wpsjxx as a left outer join wpbnloc as b on a.sbk=b.wlreader where zt=7
update A set po=(select widpo from wpidcard where widno=A.WLK)
declare R_PO cursor
static
for select po,ygbh,wlk,wlstep from A
declare
@po varchar(7),
@ygbh varchar(8),
@wlk varchar(8),
@wlstep varchar(3), @sql varchar(8000)
open R_PO
fetch next from R_PO into @po,@ygbh,@wlk,@wlstep
while (@@fetch_status=0)
begin
IF OBJECT_ID('''R'+'+@po+''') IS NULL --問題1
begin
select @sql= ' update ' + 'R'+@po +' set tkm5eno = '+@ygbh+' where tkm5id = '+@wlk+' and tkm5step = '+@wlstep+' '
exec(@sql)
end
fetch next from R_PO into @po,@ygbh,@wlk,@wlstep
end
close R_PO
deallocate R_PO
--問題2 2 此游标结合存储过程如何使用,并且每10分钟执行1次,如何实现? 放在作業裡每十分鐘執行一次
begin
select @sql= ' update ' + 'R'+@po +' set tkm5eno = '+@ygbh+' where tkm5id = '+@wlk+' and tkm5step = '+@wlstep+' '
exec(@sql)
end
现在试一下roy_88先生提供的答案。
谢谢roy_88先生,这样可以了,能不能讲一下是什么原因吗?谢谢了
create table 'R'+@po 提示 R 附近语法错误
--兩邊多了引號
select object_id('T'+'a')
select object_id('''ta''')
期待大侠们回复!
from wpsjxx as a left outer join wpbnloc as b on a.sbk=b.wlreader
join wpidcard c on a.wlk=c.widno
where zt=7 declare @count int,@sql varchar(8000)
select @count=count(*) from #A --考虑到@sql总长限制,将每50条update语句分批执行
while @count>0
begin
set @sql=''
select top 50 @sql=@sql+ 'update ' + 'R'+rtrim(widpo) +' set tkm5eno ='+rtrim(ygbh)+' where tkm5id ='+rtrim(wlk)+' and tkm5step = '+rtrim(wlstep)+';' from #A
exec(@sql)
set @count=@count-50
end
from wpsjxx as a left outer join wpbnloc as b on a.sbk=b.wlreader
join wpidcard c on a.wlk=c.widno
where zt=7 declare @count int,@sql varchar(8000)
select @count=count(*) from #A
while @count>0
begin
set @sql='' select top 50 @sql=@sql+ 'update ' + 'R'+rtrim(widpo) +' set tkm5eno ='+rtrim(ygbh)+' where tkm5id ='+rtrim(wlk)+' and tkm5step = '+rtrim(wlstep)+';'
from #A exec(@sql)
delete top(50) from #A
set @count=@count-50
end
1, select a.ygbh,a.wlk,a.sbk,b.wlstep,c.widpo into #A
from wpsjxx as a left outer join wpbnloc as b on a.sbk=b.wlreader
join wpidcard c on a.wlk=c.widno
where zt=7
其中连接的第二个表,只用一个 join 不会有什么问题吧?2, 因为我这里在批量更新的同时必须要判断 表 是否存在,如果不存在是需要创建的。期待ing,谢谢!
但是更新到数据库中就变成 43003 ,为何将前面的 0 给去掉了2, 游标在什么情况下可以使用,还是在任何情况下尽量不使用3, 我打算增加事务回滚,但是不知道如何将错误信息导入到指定的文本文件中。期待ing ……