Sql Developer Try Again or Abort
Summary: in this tutorial, you will learn how to use the SQL Server TRY CATCH construct to handle exceptions in stored procedures.
SQL Server Endeavor Catch overview
The TRY Catch construct allows you to gracefully handle exceptions in SQL Server. To use the TRY Grab construct, you offset place a grouping of Transact-SQL statements that could cause an exception in a Brainstorm TRY...Stop TRY cake as follows:
BEGIN TRY -- statements that may crusade exceptions Finish Effort
Code language: SQL (Structured Query Language) ( sql ) Then you use a Brainstorm Catch...END Grab block immediately after the TRY block:
BEGIN Take hold of -- statements that handle exception End CATCH
Code language: SQL (Structured Query Language) ( sql ) The following illustrates a consummate TRY CATCH construct:
BEGIN Try -- statements that may cause exceptions END Attempt BEGIN Take hold of -- statements that handle exception Terminate Grab
Code language: SQL (Structured Query Language) ( sql ) If the statements between the TRY block consummate without an mistake, the statements between the Grab cake will not execute. All the same, if any argument within the Attempt block causes an exception, the command transfers to the statements in the Catch block.
The CATCH block functions
Inside the Catch block, you tin use the following functions to get the detailed information on the error that occurred:
-
ERROR_LINE()returns the line number on which the exception occurred. -
ERROR_MESSAGE()returns the complete text of the generated fault bulletin. -
ERROR_PROCEDURE()returns the proper noun of the stored process or trigger where the mistake occurred. -
ERROR_NUMBER()returns the number of the error that occurred. -
ERROR_SEVERITY()returns the severity level of the error that occurred. -
ERROR_STATE()returns the land number of the error that occurred.
Annotation that y'all only use these functions in the Catch block. If you use them outside of the Grab block, all of these functions will return NULL.
Nested TRY Catch constructs
You can nest Try CATCH construct inside some other Attempt Grab construct. However, either a TRY block or a CATCH block can incorporate a nested TRY CATCH, for instance:
Begin TRY --- statements that may cause exceptions End Try Brainstorm Catch -- statements to handle exception BEGIN Effort --- nested TRY block End TRY BEGIN Take hold of --- nested CATCH block Finish Grab Stop CATCH
Code language: SQL (Structured Query Linguistic communication) ( sql ) SQL Server Endeavour CATCH examples
Showtime, create a stored process named usp_divide that divides ii numbers:
CREATE PROC usp_divide( @a decimal, @b decimal, @c decimal output ) AS BEGIN Brainstorm Try SET @c = @a / @b; END TRY BEGIN Take hold of SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_SEVERITY() Every bit ErrorSeverity ,ERROR_STATE() Every bit ErrorState ,ERROR_PROCEDURE() As ErrorProcedure ,ERROR_LINE() Equally ErrorLine ,ERROR_MESSAGE() Equally ErrorMessage; Stop CATCH END; GO
Lawmaking language: SQL (Structured Query Language) ( sql ) In this stored procedure, we placed the formula inside the Try block and chosen the CATCH cake functions ERROR_* inside the CATCH block.
2nd, phone call the usp_divide stored procedure to divide 10 by 2:
DECLARE @r decimal; EXEC usp_divide 10, 2, @r output; PRINT @r;
Code language: SQL (Structured Query Language) ( sql ) Here is the output
Code language: SQL (Structured Query Language) ( sql )
5
Because no exception occurred in the Try cake, the stored procedure completed at the TRY block.
3rd, attempt to divide 20 by zero past calling the usp_divide stored procedure:
DECLARE @r2 decimal; EXEC usp_divide 10, 0, @r2 output; PRINT @r2;
Code language: SQL (Structured Query Linguistic communication) ( sql ) The following movie shows the output:
Because of segmentation by zero error which was acquired by the formula, the control was passed to the statement inside the CATCH block which returned the error'southward detailed information.
SQL Serer Attempt CATCH with transactions
Inside a Take hold of cake, you can test the state of transactions by using the XACT_STATE() part.
- If the
XACT_STATE()role returns -ane, it means that an uncommittable transaction is awaiting, yous should event aROLLBACK TRANSACTIONstatement. - In instance the
XACT_STATE()function returns 1, it means that a committable transaction is pending. You tin can event aCOMMIT TRANSACTIONargument in this case. - If the
XACT_STATE()part return 0, it means no transaction is awaiting, therefore, you lot don't demand to take whatsoever action.
It is a adept practice to examination your transaction state earlier issuing a COMMIT TRANSACTION or ROLLBACK TRANSACTION argument in a Take hold of block to ensure consistency.
Using Attempt Take hold of with transactions example
Get-go, set up 2 new tables sales.persons and sales.deals for demonstration:
CREATE Table sales.persons ( person_id INT PRIMARY KEY IDENTITY, first_name NVARCHAR(100) NOT NULL, last_name NVARCHAR(100) NOT Nix ); CREATE Table sales.deals ( deal_id INT Principal KEY IDENTITY, person_id INT NOT Goose egg, deal_note NVARCHAR(100), Foreign KEY(person_id) REFERENCES sales.persons( person_id) ); insert into sales.persons(first_name, last_name) values ('John','Doe'), ('Jane','Doe'); insert into sales.deals(person_id, deal_note) values (1,'Deal for John Doe');
Lawmaking language: SQL (Structured Query Language) ( sql ) Next, create a new stored process named usp_report_error that volition exist used in a Take hold of block to report the detailed information of an error:
CREATE PROC usp_report_error Every bit SELECT ERROR_NUMBER() Equally ErrorNumber ,ERROR_SEVERITY() AS ErrorSeverity ,ERROR_STATE() AS ErrorState ,ERROR_LINE () AS ErrorLine ,ERROR_PROCEDURE() AS ErrorProcedure ,ERROR_MESSAGE() AS ErrorMessage; Get
Code linguistic communication: SQL (Structured Query Language) ( sql ) Then, develop a new stored procedure that deletes a row from the sales.persons table:
CREATE PROC usp_delete_person( @person_id INT ) AS Brainstorm Brainstorm TRY Begin TRANSACTION; -- delete the person DELETE FROM sales.persons WHERE person_id = @person_id; -- if DELETE succeeds, commit the transaction COMMIT TRANSACTION; END TRY Brainstorm Grab -- report exception EXEC usp_report_error; -- Test if the transaction is uncommittable. IF (XACT_STATE()) = -1 Brainstorm Impress N'The transaction is in an uncommittable state.' + 'Rolling back transaction.' ROLLBACK TRANSACTION; END; -- Test if the transaction is committable. IF (XACT_STATE()) = 1 BEGIN Impress Due north'The transaction is committable.' + 'Committing transaction.' COMMIT TRANSACTION; END; END Take hold of Finish; GO
Code linguistic communication: SQL (Structured Query Linguistic communication) ( sql ) In this stored procedure, we used the XACT_STATE() function to bank check the state of the transaction before performing COMMIT TRANSACTION or ROLLBACK TRANSACTION inside the Catch block.
After that, call the usp_delete_person stored procedure to delete the person id 2:
Code language: SQL (Structured Query Language) ( sql )
EXEC usp_delete_person two;
At that place was no exception occurred.
Finally, call the stored process usp_delete_person to delete person id 1:
Lawmaking language: SQL (Structured Query Language) ( sql )
EXEC usp_delete_person ane;
The following error occurred:
In this tutorial, y'all have learned how to utilise the SQL Server Attempt CATCH construct to handle exceptions in stored procedures.
Source: https://www.sqlservertutorial.net/sql-server-stored-procedures/sql-server-try-catch/
Post a Comment for "Sql Developer Try Again or Abort"