（1）
set ANSI_NULLS ON --见图1
set QUOTED_IDENTIFIER ON
go ALTER proc [dbo].[procname]
as
begin
begin transaction
DECLARE @myerror INT
set @myerror=0
delete from tbname
SET @myerror=@myerror+@@ERROR
insert into tbname2 select * from tbname where a>
SET @myerror=@myerror+@@ERROR IF @myerror>0
begin
rollback transaction
end
else
begin
commit transaction
end end
图1
当ANSI_NULLS置ON，比较要用is而不是=
set XACT_ABORT ON ---如果不设置该项为ON，在sql中默认为OFF，那么只只回滚产生错误的 Transact-SQL 语句；设为ON，回滚整个事务 begin tran t1 ---启动一个事务 update [water].[dbo].[ErrorInf] set ErrorMessage='test' where ID=6 insert into [water].[dbo].[ErrorInf]([ID],ErrorMessage,[Description])Values(1,'test1','test1') commit tran t1 ---提交事务
sql2005支持try
BEGIN TRY
BEGIN TRANSACTION
insert into dbo.area values('')
insert into dbo.area values('')
select 1/0
insert into dbo.area values('')
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY() RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
+
20160822
alter proc P002
(
@i2 varchar(20)
)
as
begin tran t001
begin try
insert into dbo.t_test4 (i2)values(@i2)
commit tran t001
end try begin catch
print convert(nvarchar, ERROR_NUMBER())+ ':返回错误号' print convert(nvarchar,ERROR_MESSAGE()) +':返回错误信息' print convert(nvarchar,ERROR_LINE()) +':返回错误所在的行' print convert(nvarchar,ERROR_PROCEDURE()) +':返回出现错误的存储过程或触发器的名称。如果在存储过程或触发器中未出现错误，该函数返回 NULL' print convert(nvarchar,ERROR_STATE()) +':返回状态' print convert(nvarchar,ERROR_SEVERITY()) +':返回严重性' rollback tran t001
end catch