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