Applying Batches, Stored Procedures and Functions Part 3
Managing Inaccuracies Plus Bugs
As soon as an individual implement any request, it gets analyzed intended for finding the coding mistakes in advance of execution. Certainty when the code is accurate, it is assembled as well as implemented. From time to time, because of the issues, like improper information, a mistake can happen in the course of implementation even when the request is code wise right. The mistakes which happen at execution time are identified as Exceptions.
Think through an instance, that a Primary Key constriction is present on the CustomerID column or attribute of the [ Customer ] . [ Details ] table or relation. The minute an individual attempts to add a customer ID which is previously present in the table or relation, a mistake happens at the time of implementing the INSERT command.
Once a database server offers database assistance to a corporate system, the mistakes can be created at the time of implementation SQL commands and these mistakes can be managed in dual methods:-
· By means of introducing mistake managing syntax in the batch using using the TRY – CATCH block.
· By means of reporting back the mistake messages to the corporate system using the RAISERROR command as well as managing the mistakes in the system.
Managing Errors with TRY – CATCH Block
A TRY – CATCH block is comprised of a TRY part trailed by a CATCH part. A TRY part is a set of SQL commands bounded in a batch, like a trigger, a function or a stored procedure. If a mistake happens in whichever command of the TRY part, the execution control is delivered to a different set of command which is bounded in a CATCH part.
A CATCH part covers SQL command which does various actions whenever a mistake happens. As a result, an accompanying CATCH part should straightaway trail a TRY part, for an instance the subsequent code:-
TRY
< Some SQL Commands for Try>
…..
CATCH
< Some SQL Commands for Catch >
…
END CATCH
When there are no mistakes in the syntax which is bounded in a TRY part, the execution control is delivered to the command instantly after the accompanying END CATCH command. In this situation, the commands bounded in the CATCH part are not implemented.
The TRY – CATCH block can be nested. Either the TRY part otherwise the CATCH part can have nested TRY – CATCH block. A CATCH part can have a surrounded TRY – CATCH block to manage the mistakes faced in the CATCH part’s syntax. In the CATCH part, an individual can practice the subsequent system functions to define the facts about the mistakes:-
· ERROR_LINE (): It yields back the line number on which the mistake has happened.
· ERROR_MESSAGE (): It stipulates the textual message which would be given back to the system. The textual message contains the data provided for some interchangeable factors, like as length, time, or objects name.
· ERROR_NUMBER (): It yields back the mistake number.
· ERROR_PROCEDURE (): It yields back the name of the trigger or stored procedure in which the mistake has happened. This system function yields back NULL if the mistake did not arise in a trigger or a stored procedure.
· ERROR_SEVERITY (): It yields back the severity.
· ERROR_STATE (): It yields back the status of the mistake.
Think through an instance. The LoanID column or attribute of the [ Customer ] . [ Loan ] table or relation in the XYZBank database is an IDENTITY column or attribute plus its data cannot be stated at the time of adding a fresh record. For this situation, if an individual state the data for the LoanID in the INSERT command, a mistake will be created. In the direction of managing this types of mistakes at the time of execution, an individual can embrace the insert command in a TRY part as well as direct the execution control to the subsequent CATCH part where the mistake facts are shown.
Managing Errors with RAISERROR Command
The RAISERROR command is cast-off to yield back the messages to the corporate systems which are implementing the SQL commands. This command practices the identical setup as a system error or else cautionary message produced by means of the database engine.
Think through an instance of a system which is implementing a batch. If a mistake happens at the time of implementing this batch, a mistake message will be created as well as it will be directed to the system. The system on opposing will contain the syntax to manage the mistake. An individual can yield back user – definite mistake message by means of the RAISERROR command too. A RAISERROR severity of 11 to 19 implemented in the TRY part is the reason why the execution control is transmitted to the accompanying CATCH part.
Think through another instance of the XYZBank database which has the login details of the customer intended for the online banking. A database designer wants to stop a particular customer from login in the online banking system for 24 hours if the customer provides incorrect password for more than 3 times repeatedly. Thus, the database design need to update the login table or relation whenever an incorrect password is encountered by the system, at the same time if the number of time is equal to three (3) then the message should be given to the user that “Your Account is Temporarily Locked, Try to Login With The Correct Password After 24 Hours.”