怎么会呢,exec('...')也是按照sql语句的顺序执行的阿,也不会跳跃式执行,你只要同时把事务回滚的语句也一块写道‘’里面就可以了 譬如: exec('insert into ..... select @iErr=@@Error if @iErr<>0 goto aErrLabel')
CREATE PROCEDURE ... ( ... @iSuccess int OUTPUT ) AS
SELECT @iSuccess = -1BEGIN TRAN UPDATE ...
IF @@ERROR <> 0 BEGIN ROLLBACK TRAN return ENDCOMMIT TRAN SELECT @iSuccess = 1GO
to QQMagicer(在IT的路上越走越远)这样可以的吗?
to WYZSC(007,重在参乎,说错问错勿怪。) 这些我是知道的,只是我要在存储过程中执行 Exec('.......') , 得不到 它的错误代码
建个显示的事务,就想WYZSC(007,重在参乎,说错问错勿怪。) 不就可以了
我是想用 exec('...') 的方式来执行动态的 sql 语句,比如declare @tbName sysname declare @sql varchar(1000) declare @iErr intdeclare Cursor cr for select distinct name from sysobjects where xtype='U'open crfetch next from cr into @tbNnamewhile @@fetch_status=0 begin set @sql='update ['+ @tbName +'] set col1=aValue where col1=aValue' exec(@sql) select @iErr=@@Error if @iErr<>0 goto aErrorLabel fetch next from cr into @tbName endclose cr deallocate cr--------------------------------如上面的语句, 在 exec('...') 处如果发生错误的话, 下面那句 select @iErr=@@Error 不会被执行 如果能够得到能够做到让 exec('...') 里面有错误也不会自动 raise 的话,就好办!
declare @tbName sysname declare @sql nvarchar(4000) declare @iErr intdeclare Cursor cr for select distinct name from sysobjects where xtype='U'open crfetch next from cr into @tbNnamewhile @@fetch_status=0 begin set @sql=N'update ['+ @tbName +'] set col1=aValue where col1=aValue set @iErr=@@error' exec sp_executesql @sql,N'@iErr int output',@iErr output if @iErr<>0 goto aErrorLabel fetch next from cr into @tbName endclose cr deallocate cr
譬如:
exec('insert into .....
select @iErr=@@Error
if @iErr<>0
goto aErrLabel')
(
...
@iSuccess int OUTPUT
)
AS
SELECT @iSuccess = -1BEGIN TRAN UPDATE ...
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
return
ENDCOMMIT TRAN
SELECT @iSuccess = 1GO
declare @sql varchar(1000)
declare @iErr intdeclare Cursor cr for select distinct name from sysobjects where xtype='U'open crfetch next from cr into @tbNnamewhile @@fetch_status=0
begin
set @sql='update ['+ @tbName +'] set col1=aValue where col1=aValue'
exec(@sql)
select @iErr=@@Error
if @iErr<>0
goto aErrorLabel fetch next from cr into @tbName
endclose cr
deallocate cr--------------------------------如上面的语句, 在 exec('...') 处如果发生错误的话, 下面那句 select @iErr=@@Error 不会被执行
如果能够得到能够做到让 exec('...') 里面有错误也不会自动 raise 的话,就好办!
declare @sql nvarchar(4000)
declare @iErr intdeclare Cursor cr for select distinct name from sysobjects where xtype='U'open crfetch next from cr into @tbNnamewhile @@fetch_status=0
begin
set @sql=N'update ['+ @tbName +'] set col1=aValue where col1=aValue set @iErr=@@error'
exec sp_executesql @sql,N'@iErr int output',@iErr output if @iErr<>0
goto aErrorLabel fetch next from cr into @tbName
endclose cr
deallocate cr
=========
那句select @iErr=@@Error 可以执行
create table A(ID int check(ID<10))
godeclare @sql nvarchar(4000)
declare @iErr intbegin tran set @sql=N'insert A select 20 set @iErr=@@error'
exec sp_executesql @sql,N'@iErr int output',@iErr output if @iErr<>0
goto ErrorHandler
ErrorHandler:
if @iErr<>0
begin
rollback tran
raiserror('操作错误,错误号为%d',12,1,@iErr)
end
else
begin
commit tran
end--删除测试环境
drop table A--结果
/*
服务器: 消息 547,级别 16,状态 1,行 1
INSERT 语句与 COLUMN CHECK 约束 'CK__A__ID__164452B1' 冲突。该冲突发生于数据库 'test',表 'A', column 'ID'。
语句已终止。
服务器: 消息 50000,级别 12,状态 1,行 17
操作错误,错误号为547*/