SQL里遇错误跳过继续执行的问题

achongsky 2006-06-15 12:19:12

小弟写了一个存储过程如下，过程是从一张表中读取一些数据存入游标变量，然后逐条读取游标中的数据，每读取一条数据后，由这些数据生成一段SQL脚本，然后再exec这段脚本，然后再读取游标的另一条记录，直到游标结束。

现在的问题是：在作业中调这个存储过程的时候遇到错误就中止了，也就是exec由游标生成的那段SQL脚本时，如果遇到错误，游标就不往下走了，但奇怪的是如果我在查询分析器里执行调存储过程的，遇错却能执行下去。

例如："exec HP_NewExec_Pro 1"，在作业中调这个就遇错中止，但在查询分析器里执行就能直到游标结束，该报错的报错，没出错的就显示影响多少行，按道理说在作业中执行和在查询分析器里执行应该是一样的吧，为什么会有这样的差别呢？望高手指点，或者能给我一个好的处理错误继续执行的方法，谢谢大家了。

-------------------存储过程HP_Newexec_Pro-------------------------

CREATE PROCEDURE HP_NewExec_Pro
(
@areaCode as int
)
AS

Declare @DelID as varchar(50),@type as varchar(10),@BaseName as varchar(15)
Declare @TableName as varchar(30),@IDName as varchar(15),@OrderID as varchar(50),@ChangeValue as varchar(8000),@Ctype as varchar(50)
Declare @FieldName as varchar(20),@NewRecord as varchar(8000),@LinkedServer as varchar(50)
Declare @ERR as Varchar(10)
DECLARE mycur_exec CURSOR
for
select Del_ID,Type,BaseName,TableName,IDName,OrderID,FieldName,ChangeValue,Ctype,
case areaCode when 0 then '[192.168.1.250]'
when 1 then '[192.168.2.250]'
when 2 then '[192.168.3.250]'
when 3 then '[192.168.4.250]'
end as Linkedserver
from Newexec..exc_UpdatedPro
where dbs.dbo.fc_Marks(Update__Mark,@areaCode)<>0 and charindex(cast(@areaCode as varchar),Sendarea)<>0
order by Del_Date,type
open mycur_exec
---------------------------------
fetch next from mycur_exec into @DelID,@type,@BaseName,@TableName,@IDname,@OrderID,@FieldName,@ChangeValue,@Ctype,@LinkedServer

while (@@FETCH_STATUS<>-1)
begin

---------------------------------------
IF @Type=1
begin

set @NewRecord='Insert '+@BaseName+'.dbo.'+@TableName+'
select *
from '+@LinkedServer+'.'+@BaseName+'.dbo.'+@Tablename+'
where '+@IDname+'='''+@OrderID+''''
--Print @NewRecord
exec(@NewRecord)
set @Err=@@ERROR
IF @Err<>0
begin
Insert Newexec..exc_ERROR
(
ERROR_ID,ERROR_Code
)
select @DelID,@ERR
end

Update Newexec..exc_UpdatedPro
set Update__Mark=dbs.dbo.fc_markzero(Update__Mark,@areaCode)
where Del_ID=@DelID

end
------------------------------------
--------------------
IF @Type=2
begin

set @NewRecord='Delete '+@BaseName+'.dbo.'+@TableName+'
where '+@IDname+'='''+@OrderID+''''
exec(@NewRecord)
set @Err=@@ERROR
IF @Err<>0

begin
Insert Newexec..exc_ERROR
(
ERROR_ID,ERROR_Code
)
select @DelID,@ERR
end
Update Newexec..exc_UpdatedPro
set Update__Mark=dbs.dbo.fc_markzero(Update__Mark,@areaCode)
where Del_ID=@DelID

end
--------------------
if @Type=3
begin
if @ctype='money'
begin
set @NewRecord='
Update '+@BaseName+'.dbo.'+@TableName+'
set ['+@FieldName+']='+@ChangeValue+',Update__Mark=UPdate__Mark
where '+@IDname+'='''+@OrderID+''''
end
if @ctype<>'money' or @ctype is null
begin
set @NewRecord='
Update '+@BaseName+'.dbo.'+@TableName+'
set ['+@FieldName+']='''+@ChangeValue+''',Update__Mark=UPdate__Mark
where '+@IDname+'='''+@OrderID+''''
end
exec(@NewRecord)
set @Err=@@ERROR
IF @Err<>0

begin
Insert Newexec..exc_ERROR
(
ERROR_ID,ERROR_Code
)
select @DelID,@ERR
end
Update Newexec..exc_UpdatedPro
set Update__Mark=dbs.dbo.fc_markzero(Update__Mark,@areaCode)
where Del_ID=@DelID

end

fetch next from mycur_exec into @DelID,@type,@BaseName,@TableName,@IDname,@OrderID,@FieldName,@ChangeValue,@Ctype,@LinkedServer

end
close mycur_exec
DEALLOCATE mycur_exec
GO

...全文