Start preparing for your Next Exam | Use coupon – TOGETHER | Avail 30% discount

Implement error handling and transactions

  1. Home
  3. Exam 70-761: Querying Data with Transact-SQL
  5. Implement error handling and transactions

Exam 70-761: Querying Data with Transact-SQL retired on 31 January, 2021.

Transact-SQL statements

We shall now discuss the categories of statements for use with Transact-SQL (T-SQL). You can find all of the statements listed in the left-hand navigation.

Data Definition Language

Data Definition Language (DDL) statements defines data structures. Use these statements to create, alter, or drop data structures in a database.

  • ALTER
  • Collations
  • CREATE
  • DROP
  • DISABLE TRIGGER
  • ENABLE TRIGGER
  • RENAME
  • UPDATE STATISTICS

Data Manipulation Language

Data Manipulation Language (DML) affect the information stored in the database. Use these statements to insert, update, and change the rows in the database.

  • BULK INSERT
  • DELETE
  • INSERT
  • UPDATE
  • MERGE
  • TRUNCATE TABLE

Permissions statements

Permissions statements determine which users and logins can access data and perform operations. For more information about authentication and access, see the Security center.

Service Broker statements

Service Broker is a feature that provides native support for messaging and queuing applications. For more information, see Service Broker.

Session settings

SET statements determine how the current session handles run time settings. For an overview, see SET statements.

SQL Server 2005 – Try and Catch Exception Handling

With SQL Server 2005, new error handling has been introduced with the TRY…CATCH processing. Basically what happens is when an error occurs the processing in the TRY block stops and processing is then picked up in the CATCH block. The following illustrates a simple example of how this is done –

StepCodeOutput
1CREATE PROCEDURE usp_ExampleProc
AS
     SELECT * FROM NonexistentTable;
GO		Command(s) completed successfully.
2EXECUTE usp_ExampleProcMsg 208, Level 16, State 1, Procedure usp_ExampleProc, Line 3
Invalid object name ‘NonexistentTable’.(Note: Processing Stops)
3BEGIN TRY
     EXECUTE usp_ExampleProc
END TRY

BEGIN CATCH
     SELECT 
          ERROR_NUMBER() as ErrorNumber,
          ERROR_MESSAGE() as ErrorMessage;
END CATCH;		208 Invalid object name ‘NonexistentTable’.(Note: Processing Continues)
4BEGIN TRY
     EXECUTE usp_ExampleProc
END TRY

BEGIN CATCH

END CATCH;		Command(s) completed successfully.(Note: Processing Continues)
  • As you can see from the above code and output that when we create the stored procedure for a non-existent table in Step 1 the procedure creates without a problem. 
  • If we run the stored procedure using the code in Step 2, we get an error message that the object does not exist.
  • If we run the stored procedure using the code in Step 3, the error is sent to the CATCH block and an error message is returned. At this point processing can continue without a problem.
  • To further illustrate this in Step 4 the stored procedure is run, the error is caught in the CATCH block, but we are not doing anything to process the error.  Normally you would have something happen, but this shows that you don’t have to have any code in the CATCH block.

The values that can be retrieved from the error are also much more detailed, then what you could get with previous versions of SQL Server.  Below is a list of the data that can be retrieved when an error occurs.

  • ERROR_NUMBER() – returns the number of the error.
  • ERROR_SEVERITY() – returns the severity.
  • ERROR_STATE() – returns the error state number.
  • ERROR_PROCEDURE() – returns the name of the stored procedure or trigger where the error occurred.
  • ERROR_LINE() – returns the line number inside the routine that caused the error.
  • ERROR_MESSAGE() – returns the complete text of the error message.

Running the same query above, but returning all of the error information is displayed below.

ERROR_NUMBERERROR_SEVERITYERROR_STATEERROR_PROCEDUREERROR_LINEERROR_MESSAGE
208161usp_ExampleProc3Invalid object name ‘NonexistentTable’.

Another nice thing about the TRY…CATCH processing is that you can nest or have multiple TRY…CATCH blocks in your code.  The following although not very practical illustrates how the error is caught and then processing continues and the error is caught again and processing continues again.

StepCodeOutput
1BEGIN TRY
     BEGIN TRY
          EXECUTE usp_ExampleProc
     END TRY

     BEGIN CATCH
          SELECT
               ERROR_NUMBER() AS ErrorNumber,
               ERROR_SEVERITY() AS ErrorSeverity;
     END CATCH;

     EXECUTE usp_ExampleProc
END TRYBEGIN CATCH
     SELECT
          ERROR_NUMBER() AS ErrorNumber,
          ERROR_SEVERITY() AS ErrorSeverity;
END CATCH;		208 Invalid object name ‘NonexistentTable’. 208 Invalid object name ‘NonexistentTable’.

TRY…CATCH (Transact-SQL)

TRY…CATCH (Transact-SQL)

Implements error handling for Transact-SQL that is similar to the exception handling in the Microsoft Visual C# and Microsoft Visual C++ languages. A group of Transact-SQL statements can be enclosed in a TRY block. If an error occurs in the TRY block, control is passed to another group of statements that is enclosed in a CATCH block.

Syntax

BEGIN TRY
{ sql_statement | statement_block }
END TRY
BEGIN CATCH
[ { sql_statement | statement_block } ]
END CATCH
[ ; ]

Features of TRY…CATCH

  • A TRY…CATCH construct catches all execution errors that have a severity higher than 10 that do not close the database connection.
  • A TRY block must be immediately followed by an associated CATCH block. Including any other statements between the END TRY and BEGIN CATCH statements generates a syntax error.
  • A TRY…CATCH construct catches all execution errors that have a severity higher than 10 that do not close the database connection.
  • A TRY block must be immediately followed by an associated CATCH block. Including any other statements between the END TRY and BEGIN CATCH statements generates a syntax error.
  • A TRY…CATCH construct cannot span multiple batches. A TRY…CATCH construct cannot span multiple blocks of Transact-SQL statements. For example, a TRY…CATCH construct cannot span two BEGIN…END blocks of Transact-SQL statements and cannot span an IF…ELSE construct.

Retrieving Error Information

In the scope of a CATCH block, the following system functions can be used to obtain information about the error that caused the CATCH block to be executed –

  • ERROR_NUMBER() returns the number of the error.
  • ERROR_SEVERITY() returns the severity.
  • ERROR_STATE() returns the error state number.
  • ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.
  • ERROR_LINE() returns the line number inside the routine that caused the error.
  • ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.

Errors Unaffected by a TRY…CATCH Construct

TRY…CATCH constructs do not trap the following conditions –

  • Warnings or informational messages that have a severity of 10 or lower.
  • Errors that have a severity of 20 or higher that stop the SQL Server Database Engine task processing for the session. If an error occurs that has severity of 20 or higher and the database connection is not disrupted, TRY…CATCH will handle the error.
  • Attentions, such as client-interrupt requests or broken client connections.
  • When the session is ended by a system administrator by using the KILL statement.

The following types of errors are not handled by a CATCH block when they occur at the same level of execution as the TRY…CATCH construct:

  • Compile errors, such as syntax errors, that prevent a batch from running.
  • Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution.
  • Object name resolution errors

THROW (Transact-SQL)

Raises an exception and transfers execution to a CATCH block of a TRY…CATCH construct in SQL Server 2017.

THROW [ { error_number | @local_variable }, { message | @local_variable }, { state | @local_variable } ] [ ; ]

THROW [ { error_number | @local_variable },
{ message | @local_variable },
{ state | @local_variable } ]
[ ; ]

NOTES

  • The statement before the THROW statement must be followed by the semicolon (;) statement terminator.
  • If a TRY…CATCH construct is not available, the statement batch is terminated. The line number and procedure where the exception is raised are set. The severity is set to 16.
  • If the THROW statement is specified without parameters, it must appear inside a CATCH block. This causes the caught exception to be raised. Any error that occurs in a THROW statement causes the statement batch to be terminated.
  • % is a reserved character in the message text of a THROW statement and must be escaped. Double the % character to return % as part of the message text, for example ‘The increase exceeded 15%% of the original value.

Differences Between RAISERROR and THROW

The following table lists differences between the RAISERROR and THROW statements.

RAISERROR statementTHROW statement
If a msg_id is passed to RAISERROR, the ID must be defined in sys.messages.The error_number parameter does not have to be defined in sys.messages.
The msg_str parameter can contain printf formatting styles.The message parameter does not accept printf style formatting.
The severity parameter specifies the severity of the exception.There is no severity parameter. The exception severity is always set to 16.
Menu