ALTER PROCEDURE [dbo].[A]
AS
BEGIN
SET NOCOUNT ON; BEGIN TRY
RAISERROR ('Error raised in TRY block.', -- Message text.
16, -- Severity.
1 -- State.
);
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT; SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(); RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
) with seterror;
END CATCH;
END
ALTER PROCEDURE B
AS
BEGIN
SET NOCOUNT ON;
exec A
if @@error<>0
print 'error occur'
else
print 'done'
END
GODECLARE @RC int
EXECUTE @RC = [dbo].[B] 结果是
Msg 50000, Level 16, State 1, Procedure A, Line 24
Error raised in TRY block.
done只有if @@error=0的时候才显示done的,为啥有了自定义的error抛出来
if @@error<>0
print 'error occur'
else
print 'done'
这个@@error还是0呢?
AS
BEGIN
SET NOCOUNT ON; BEGIN TRY
RAISERROR ('Error raised in TRY block.', -- Message text.
16, -- Severity.
1 -- State.
);
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT; SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(); RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
) with seterror;
END CATCH;
END
ALTER PROCEDURE B
AS
BEGIN
SET NOCOUNT ON;
exec A
if @@error<>0
print 'error occur'
else
print 'done'
END
GODECLARE @RC int
EXECUTE @RC = [dbo].[B] 结果是
Msg 50000, Level 16, State 1, Procedure A, Line 24
Error raised in TRY block.
done只有if @@error=0的时候才显示done的,为啥有了自定义的error抛出来
if @@error<>0
print 'error occur'
else
print 'done'
这个@@error还是0呢?
成功执行exec A 所以会=0
存储过程A,在怎么样也会成功执行的因为它用到了try ...catch
所以@@error老是等于0
as
begin try
declare @I int
raiserror 50001N'a'end try
begin catch
raiserror 50001 N'error'
end catch
go
create proc P3
as
begin try
exec P1
print 'done'
end try
begin catch
print 'error occur'
end catch
XACT_STATE 指示请求是否有活动的用户事务,以及是否能够提交该事务USE AdventureWorks;
GO-- SET XACT_ABORT ON will render the transaction uncommittable
-- when the constraint violation occurs.
SET XACT_ABORT ON;BEGIN TRY
BEGIN TRANSACTION;
-- A FOREIGN KEY constraint exists on this table. This
-- statement will generate a constraint violation error.
DELETE FROM Production.Product
WHERE ProductID = 980; -- If the delete operation succeeds, commit the transaction. The CATCH
-- block will not execute.
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
-- Test XACT_STATE for 0, 1, or -1.
-- If 1, the transaction is committable.
-- If -1, the transaction is uncommittable and should
-- be rolled back.
-- XACT_STATE = 0 means there is no transaction and
-- a commit or rollback operation would generate an error. -- Test whether the transaction is uncommittable.
IF (XACT_STATE()) = -1
BEGIN
PRINT 'The transaction is in an uncommittable state.' +
' Rolling back transaction.'
ROLLBACK TRANSACTION;
END; -- Test whether the transaction is active and valid.
IF (XACT_STATE()) = 1
BEGIN
PRINT 'The transaction is committable.' +
' Committing transaction.'
COMMIT TRANSACTION;
END;
END CATCH;
GO