SQL Server: Error handling and transaction control

Directory:

Interpreting error messages

RAISERROR

THROW

Instance

Use @ @ERROR

Using Xact_abort

Using Try/catch

Transactional statements in real-world

Delete

Update

Bank to withdraw money

Interpreting error messages

MSG 547, Level A, state 0, line 11The INSERT statement conflicted with the FOREIGN KEY constraint "fk_products_categories ". The conflict occurred in database "TSQL2012", table "Production.categories", Column ' CategoryID '.

Error number

SQL Server error messages are numbered from 1~49999

custom error message starting from 50001

Error number 50000 is prepared for custom information that does not have an error number.

Severity level

SQL Server altogether has 26 severity levels of 0~25.

SQL Server logs and Windows application logs are logged in severity level >= 16

severity level 19~25 can only be handled by members that the sysadmin feels

The severity level 20~25 is considered a fatal error. The terminal connection is interrupted and all open transactions are rolled back.

The severity level 0~10 is just a hint message.

State int type, maximum value 127, MS internal purposes

Error message supports 255 Unicode characters

SQL Server error messages are in sys.messages

You can add custom error messages with sp_addmessage

RAISERROR (no interruption of transactions)

Simple pass-through information can use level 0~9.

If you have the sysadmin role, you can use the WITH LOG option and set a severity level >20 error. When error occurs, SQL Server interrupts the connection.

Use the NOWAIT option to send messages directly without waiting for the tournament buffer

RAISERROR (' Error in usp_insertcategories stored procedure ', 0);--Formatting the RAISERROR stringraiserror (' Error in % stored procedure ', 0, N ' usp_insertcategories ');--In addition, can use a variable:godeclare @message as Nvarch AR (+) = N ' Error in% stored procedure '; RAISERROR (@message, 0, N ' usp_insertcategories ');--and you can add the formatting outside RAISERROR using the FORMATM Essage Function:godeclare @message as NVARCHAR (+) = N ' Error in% stored procedure '; SELECT @message = FormatMessage (@message, N ' usp_insertcategories '); RAISERROR (@message, 16, 0);

THROW (Will interrupt transaction)

--You can issue a simple THROW as Follows:throw 50000, ' Error in usp_insertcategories stored procedure ', 0;--Because THR OW does not allow formatting of the message parameter, you can use FormatMessage () Godeclare @message as NVARCHAR (+) = N ' Error in% stored procedure '; SELECT @message = FormatMessage (@message, N ' usp_insertcategories '); THROW 50000, @message, 0;

--RAISERROR does not normally terminate a batch:raiserror (' Hi there ', 16, 0); PRINT ' RAISERROR error '; --printsgo--However, THROW does terminate the Batch:throw 50000, ' Hi there ', 0; PRINT ' THROW error '; --Does not Printgo

Instance

Use @ @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 ' + CA    ST (@errnum as VARCHAR); END 

DECLARE @errnum as int;    BEGIN TRAN;    SET Identity_insert production.products on; --insert #1 would fail because of duplicate primary key Insert into Production.Products (ProductID, ProductName, Supplie    RIDs, 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 'll 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 ProductiMnl    Products OFF; IF @ @TRANCOUNT > 0 COMMIT tran;--Remove the inserted rowdelete from production.products WHERE productid = 101; PRINT ' Deleted ' + CAST (@ @ROWCOUNT as VARCHAR) + ' rows ';

Using Xact_abort

With Xact_abort, an error occurs in the statement, and the entire statement is aborted.

SET Xact_abort on; PRINT ' before error '; 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 identity_insert production.products OFF; PRINT ' after error '; Goprint ' New batch '; SET Xact_abort OFF;

--Using THROW with Xact_abort. Use TSQL2012; Goset Xact_abort on; PRINT ' before error '; THROW 50000, ' Error in usp_insertcategories stored procedure ', 0; PRINT ' after error '; Goprint ' New batch '; SET Xact_abort OFF;

@ @ERROR after using Xact_abort in the second example, the second statement is invalid.

DECLARE @errnum as int; SET Xact_abort on;    BEGIN TRAN;    SET Identity_insert production.products on; --insert #1 would fail because of duplicate primary key Insert into Production.Products (ProductID, ProductName, Supplie    RIDs, 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 ' Error in first INSERT ';     END; --Insert #2 no longer succeeds insert into Production.Products (ProductID, ProductName, SupplierID, CategoryID, Uni    Tprice, discontinued) VALUES (101, N ' Test2:bad CategoryID ', 1, 1, 18.00, 0);    SET @errnum = @ @ERROR; If @errnum <> 0 BEGIN-take actions based on the error IF @ @TRANCOUNT > 0 Rollbac            K TRAN;        PRINT ' Error in second INSERT ';     END;    SET identity_insert production.products OFF; IF @ @TRANCOUNT > 0 COMMIT TRAN; GO DELETE from production.products WHERE productid = 101; PRINT ' Deleted ' + CAST (@ @ROWCOUNT as VARCHAR) + ' rows '; SET Xact_abort OFF; Goselect xact_state (), @ @TRANCOUNT;

Using Try/catch

Format

--transactions extend Batchesbegin TRY BEGIN TRANSACTION   INSERT into Sales.SalesOrderHeader ...--succeeds  INSERT into Sales.SalesOrderDetail--fails COMMIT TRANSACTION--If no errors, TRANSACTION completesend trybegin CATCH --inserted rows still exist in Sales.SalesOrderHeader SELECT error_number () ROLLBACK TRANSACTION--any TRANSACTION work UN Doneend CATCH;

 BEGIN Trybegin 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); INSERT into Production.Products (ProductID, ProductName, SupplierID, CategoryID, UnitPrice, discontinued) VALUES (101    , N ' Test2:bad CategoryID ', 1, 10, 18.00, 0); SET identity_insert production.products OFF; COMMIT TRAN; END trybegin CATCH IF error_number () = 2627--Duplicate key violation BEGIN PRINT ' Primary key viola        tion ';        END ELSE IF error_number () = 547--Constraint violations BEGIN PRINT ' Constraint violation ';        END ELSE BEGIN PRINT ' unhandled error ';    END; IF @ @TRANCOUNT > 0 ROLLBACK TRANSACTION; END CATCH; 

--Revise the CATCH block using variables to capture error information and re-raise the error using RAISERROR. Use TSQL2012; Goset NOCOUNT on;declare @error_number as int, @error_message as NVARCHAR (+), @error_severity as int;    BEGIN Trybegin TRAN;    SET Identity_insert production.products on; INSERT into Production.Products (ProductID, ProductName, SupplierID, CategoryID, UnitPrice, discontinued) VA    Lues (1, N ' Test1:ok CategoryID ', 1, 1, 18.00, 0); INSERT into Production.Products (ProductID, ProductName, SupplierID, CategoryID, UnitPrice, discontinued) VA    Lues (101, N ' Test2:bad CategoryID ', 1, 10, 18.00, 0);    SET identity_insert production.products OFF; COMMIT TRAN;    END trybegin CATCH SELECT xact_state () as ' xact_state ', @ @TRANCOUNT as ' @ @TRANCOUNT ';    SELECT @error_number = Error_number (), @error_message = Error_message (), @error_severity = Error_severity ();    RAISERROR (@error_message, @error_severity, 1); IF @ @TRANCOUNT > 0 ROLLBACK TRansaction; END CATCH;

--Use a THROW statement without parameters Re-raise (re-throw) The original error message and send it back to the client. Use TSQL2012; Gobegin Trybegin 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);    INSERT into Production.Products (ProductID, ProductName, SupplierID, CategoryID,         UnitPrice, discontinued)        VALUES (101, N ' Test2:bad CategoryID ', 1, ten, 18.00, 0);    SET identity_insert production.products OFF; COMMIT TRAN; END trybegin CATCH    SELECT xact_state () as ' xact_state ', @ @TRANCOUNT as ' @ @TRANCOUNT ';    IF @ @TRANCOUNT > 0 ROLLBACK TRANSACTION;    THROW; END CATCH; Goselect xact_state () as ' xact_state ', @ @TRANCOUNT as ' @ @TRANCOUNT ';

Transactional statements in real-world

Delete

--Delete Create PROCEDURE [dbo]. [Students_delete] (@ID int) With EXECUTE as Callerasbegin    --check to make sure the ID does exist--if not    does, return error    DECLARE @exis ting as int = 0    SELECT @existing = count (ID) from      Students    WHERE id = @ID        IF @existing <> 1    begi N        RAISERROR (' ID does not exist ', 1, 1)        RETURN 0    END        --attempt Delete        delete from [dbo].[ Students]        WHERE ID = @ID             --check to see if update occured         --and return status        if @ @ROWCOUNT = 1            B Egin                INSERT into Studentdeletelog                 VALUES (SUSER_SNAME (), @ID, GETDATE ())                RETURN 1            END                    ELSE             RETURN 0ENDGO

Update

CREATE PROCEDURE [dbo]. [Students_update] (@ID int, @LASTNAME varchar (), @FIRSTNAME varchar, @STATE varchar, @PHONE varcha R (), @EMAIL varchar, @GRADYEAR int, @GPA decimal (20,10), @PROGRAM varchar, @NEWSLETTER bit ) Asbegin--check to make sure the ID does exist--if not does, return error DECLARE @existing as int = 0 Selec  T @existing = count (id) from Students WHERE ID = @ID IF @existing <> 1 BEGIN RAISERROR (' ID Does not exist ', 1, 1) RETURN 0 END--can not subscribe to newsletter if e-mail is null if (@email is null ) SET @NEWSLETTER = 0--attempt updateupdate [dbo].   [Students] SET [LASTNAME] = @LASTNAME, [FIRSTNAME] = @FIRSTNAME, [state] = @STATE, [PHONE] = @PHONE, [EMAIL]  = @EMAIL, [gradyear] = @GRADYEAR, [GPA] = @GPA, [program] = @PROGRAM, [NEWSLETTER] = @NEWSLETTER WHERE ID = @ID--check to see if update occured--and return status if @ @ROWCOUNT = 1 return 1 ELSE RETURN 0ENDGO

Bank to withdraw money

BEGIN TRAN;    If not EXISTS (        SELECT * from Accounts with (UPDLOCK)  --only the current transaction can see        WHERE AccountID = 47387438 and Balance > =    N)    BEGIN        roolback TRAN;        THROW 50000, ' Tobias is too poor ', 1;    END    UPDATE Accounts SET        Balance-=400    WHERE AccountID = 47387438; COMMIT tran;--Bank take money efficient version begin TRAN;    UPDATE Accounts SET        Balance-=    , WHERE AccountID = 47387438 and Balance >=    IF (@ @ROWCOUNT <> 1)    BEGIN        ROLLBACK TRAN;        THROW 50000, ' Tobias is too poor ', 1;    Endcommit TRAN;

Reference documents

Database Engine Error severities

Https://msdn.microsoft.com/en-us/library/ms164086.aspx

SET xact_abort (Transact-SQL)

Https://msdn.microsoft.com/zh-tw/library/ms188792.aspx

SQL Server: Error handling and transaction control