Sql Code Masterz

Below are the different functions use inside CATCH block:- (1)ERROR_NUMBER:- The number of the error that occurred. This is similar to @@ERROR except that it will return the same number for the duration of the CATCH block. (2)ERROR_MESSAGE:- The complete text of the error message including any substitute parameters such as object names. (3)ERROR_LINE:- This is the line number of the batch or stored procedure where the error occurred. (4)ERROR_SEVERITY:- This is the severity of the error. The CATCH block only fires for errors with severity 11 or higher. Error severity from 11 to 16 are typically user or code errors. Severity levels from 17 to 25 are usually software or hardware errors where processing may not be able to continue. (5)ERROR_STATE:- This is sometimes used by the system to return more information about the error. (6)ERROR_PROCEDURE:- If the error was generated inside a stored procedure this will hold the name of the proce...

Many programming languages have a fairly consistent behaviour when there is a run-time error. Common is that the execution simply terminates in case of an error, unless you have set up an exception handler that takes care the error. In other languages, some error variable is set and you have to check this variable. T-SQL is confusing, because depending on what error that occurs and in which context it occurs, SQL Server can take no less than four different actions. I first give an overview of these alternatives, followed by a more detailed discussion of which errors that cause which actions. I then discuss two special cases: trigger context and user-defined functions. The Possible Actions These are the four main possible actions SQL Server can take: Statement-termination. The current statement is aborted and rolled back. Execution continues on the next statement. Any open transaction is not rolled back. @@error is set to the number of the error. Since the statement is rolled back...