使用 @@ERROR

来源：互联网发布：算法图解 pdf 编辑：程序博客网时间：2024/06/11 22:40

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

0 0