使用 @@ERROR

DECLARE @errnum AS int;
BEGIN TRAN;
SET IDENTITY_INSERT Production.Products ON;
INSERT INTO Production.Products(productid, productname, supplierid, categoryid, unitprice, discontinued)
    VALUES(1, N'Test1: Ok categoryid', 1, 1, 18.00, 0);
SET @errnum = @@ERROR; 
IF @errnum <> 0 -- Handle the error
    BEGIN 
        PRINT 'Insert into Production.Products failed with error ' + CAST(@errnum AS VARCHAR);
    END
复制代码
复制代码
DECLARE @errnum AS int;
BEGIN TRAN;
    SET IDENTITY_INSERT Production.Products ON;
    -- Insert #1 will fail because of duplicate primary key
    INSERT INTO Production.Products(productid, productname, supplierid, categoryid,     unitprice, discontinued)
        VALUES(1, N'Test1: Ok categoryid', 1, 1, 18.00, 0);
    SET @errnum = @@ERROR;
    IF @errnum <> 0
        BEGIN 
            IF @@TRANCOUNT > 0 ROLLBACK TRAN;
            PRINT 'Insert #1 into Production.Products failed with error ' + CAST(@errnum AS VARCHAR);
        END; 
    -- Insert #2 will succeed
    INSERT INTO Production.Products(productid, productname, supplierid, categoryid,     unitprice, discontinued)
        VALUES(101, N'Test2: Bad categoryid', 1, 1, 18.00, 0);
    SET @errnum = @@ERROR;
    IF @errnum <> 0
        BEGIN 
            IF @@TRANCOUNT > 0 ROLLBACK TRAN;
            PRINT 'Insert #2 into Production.Products failed with error ' + CAST(@errnum AS VARCHAR);
        END; 
    SET IDENTITY_INSERT Production.Products OFF;
    IF @@TRANCOUNT > 0 COMMIT TRAN;
-- Remove the inserted row
DELETE FROM Production.Products WHERE productid = 101;
PRINT 'Deleted ' + CAST(@@ROWCOUNT AS VARCHAR) + ' rows';
复制代码
DECLARE @errnum AS int;BEGIN TRAN;SET IDENTITY_INSERT Production.Products ON;INSERT INTO Production.Products(productid, productname, supplierid, categoryid, unitprice, discontinued)    VALUES(1, N'Test1: Ok categoryid', 1, 1, 18.00, 0);SET @errnum = @@ERROR; IF @errnum <> 0 -- Handle the error    BEGIN         PRINT 'Insert into Production.Products failed with error ' + CAST(@errnum AS VARCHAR);    END
复制代码
复制代码
DECLARE @errnum AS int;BEGIN TRAN;    SET IDENTITY_INSERT Production.Products ON;    -- Insert #1 will fail because of duplicate primary key    INSERT INTO Production.Products(productid, productname, supplierid, categoryid,     unitprice, discontinued)        VALUES(1, N'Test1: Ok categoryid', 1, 1, 18.00, 0);    SET @errnum = @@ERROR;    IF @errnum <> 0        BEGIN             IF @@TRANCOUNT > 0 ROLLBACK TRAN;            PRINT 'Insert #1 into Production.Products failed with error ' + CAST(@errnum AS VARCHAR);        END;     -- Insert #2 will succeed    INSERT INTO Production.Products(productid, productname, supplierid, categoryid,     unitprice, discontinued)        VALUES(101, N'Test2: Bad categoryid', 1, 1, 18.00, 0);    SET @errnum = @@ERROR;    IF @errnum <> 0        BEGIN             IF @@TRANCOUNT > 0 ROLLBACK TRAN;            PRINT 'Insert #2 into Production.Products failed with error ' + CAST(@errnum AS VARCHAR);        END;     SET IDENTITY_INSERT Production.Products OFF;    IF @@TRANCOUNT > 0 COMMIT TRAN;-- Remove the inserted rowDELETE FROM Production.Products WHERE productid = 101;PRINT 'Deleted ' + CAST(@@ROWCOUNT AS VARCHAR) + ' rows';
复制代码
使用 XACT_ABORT