TRY…CATCH…通用格式

USE Dorben

GO

BEGIN TRY

    -- Generate a divide-by-zero error.

    SELECT 1/0;

END TRY

BEGIN CATCH

    SELECT

        ERROR_NUMBER()        AS ErrorNumber,

        ERROR_SEVERITY()    AS ErrorSeverity,

        ERROR_STATE()        AS ErrorState,

        ERROR_PROCEDURE()    AS ErrorProcedure,

        ERROR_LINE()        AS ErrorLine,

        ERROR_MESSAGE()        AS ErrorMessage;

END CATCH

 

 

CREATE TABLE TEST
(
 TID INT IDENTITY PRIMARY KEY,
 TNAME VARCHAR(20)
)

 

 --将 SELECT 1 插入TEST 不复核表的数据类型，造成异常

DECLARE @ReturnValue INT
SELECT @ReturnValue = 2

Begin Transaction
BEGIN TRY
 INSERT INTO TEST
 SELECT 1
 union all
 SELECT 'B'
 union all
 SELECT 'C'
 SELECT @ReturnValue = 1
    commit Transaction
END TRY
BEGIN CATCH
    rollback Transaction
    PRINT '发生异常'
    SELECT ERROR_MESSAGE() AS ErrorMessage
 SELECT @ReturnValue = 0
END CATCH

SELECT @ReturnValue

GO