This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Design and develop database solutions (35–40%)
   --> Write advanced T-SQL code
      --> Implement error handling

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

Robust database applications must be able to detect, handle, and recover from errors gracefully. Whether a stored procedure is inserting data, updating records, processing transactions, or calling external services, unexpected conditions such as constraint violations, deadlocks, conversion failures, or missing objects can occur. Proper error handling prevents data corruption, improves application reliability, and provides meaningful feedback to developers and users.

SQL Server provides several built-in mechanisms for implementing error handling, including:

• TRY...CATCH
• THROW
• RAISERROR (legacy)
• Error information functions
• Transaction control (BEGIN TRANSACTION, COMMIT, ROLLBACK)
• XACT_STATE()
• SET XACT_ABORT

For the DP-800: Developing AI-Enabled Database Solutions exam, you should understand how to implement structured error handling, manage transactions during errors, retrieve error details, and determine when to use THROW versus RAISERROR.

Why Error Handling Matters

Without proper error handling:

• Transactions may remain partially completed.
• Data consistency may be compromised.
• Applications may receive unhelpful error messages.
• Resources may remain locked.
• Troubleshooting becomes difficult.

Good error handling:

• Preserves data integrity.
• Simplifies debugging.
• Improves user experience.
• Supports logging and auditing.
• Enables reliable transaction management.

Common Types of SQL Errors

Examples include:

• Divide-by-zero errors
• Constraint violations
• Duplicate key violations
• Invalid object names
• Data conversion failures
• Deadlocks
• Arithmetic overflow
• Permission errors
• Transaction failures
• Lock timeouts

Example:

SELECT 100 / 0;

Produces:

Divide by zero error encountered.

TRY…CATCH

The primary error handling construct in SQL Server is the TRY...CATCH block.

General syntax:

BEGIN TRY
    -- T-SQL statements
END TRY
BEGIN CATCH
    -- Error handling
END CATCH;

If an error occurs inside the TRY block, execution immediately transfers to the CATCH block.

Simple TRY…CATCH Example

BEGIN TRY
    SELECT 100 / 0;
END TRY
BEGIN CATCH
    PRINT 'An error occurred.';
END CATCH;

Output:

An error occurred.

Handling Insert Errors

Example:

BEGIN TRY
    INSERT INTO Customers(CustomerID)
    VALUES (1);
END TRY
BEGIN CATCH
    PRINT 'Insert failed.';
END CATCH;

If a duplicate key exists, execution moves to the CATCH block.

Retrieving Error Information

Within a CATCH block, SQL Server provides several built-in functions.

Example:

BEGIN TRY
    SELECT 100 / 0;
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage,
        ERROR_LINE() AS ErrorLine;
END CATCH;

ERROR_MESSAGE()

This function returns the descriptive text of the error.

Example:

SELECT ERROR_MESSAGE();

Possible output:

Divide by zero error encountered.

ERROR_NUMBER()

Returns SQL Server’s internal error number.

Example:

8134

Error numbers help identify specific issues and are useful for logging and troubleshooting.

ERROR_LINE()

Returns the line where the error occurred.

Example:

15

This simplifies debugging of large stored procedures.

ERROR_PROCEDURE()

Returns the stored procedure that generated the error.

Example:

usp_ProcessOrder

Returns NULL if the error occurred outside a stored procedure.

THROW

THROW is the modern method for raising exceptions.

Syntax:

THROW;

Or:

THROW
50001,
'Customer not found.',
1;

Parameters:

• Error number (50000 or greater for user-defined errors)
• Error message
• State

Re-Throwing an Error

Inside a CATCH block:

BEGIN TRY
    SELECT 100 / 0;
END TRY
BEGIN CATCH
    THROW;
END CATCH;

This preserves the original error information, including the error number, message, severity, state, and line number.

THROW vs RAISERROR

RAISERROR is the older method for generating custom errors. It remains supported for backward compatibility but Microsoft recommends using THROW for new development.

Example:

RAISERROR
(
'Invalid customer.',
16,
1
);

Equivalent modern syntax:

THROW
50001,
'Invalid customer.',
1;

Comparing THROW and RAISERROR

Exam Tip: Unless maintaining legacy code, prefer THROW over RAISERROR.

Transactions and Error Handling

Errors often occur during transactions.

Example:

BEGIN TRANSACTION;
UPDATE Accounts
SET Balance = Balance - 100
WHERE AccountID = 1;
UPDATE Accounts
SET Balance = Balance + 100
WHERE AccountID = 2;
COMMIT;

If the second update fails, the first update may already have succeeded, resulting in inconsistent data unless the transaction is rolled back.

TRY…CATCH with Transactions

BEGIN TRY
    BEGIN TRANSACTION;
    UPDATE Accounts
    SET Balance = Balance - 100
    WHERE AccountID = 1;
    UPDATE Accounts
    SET Balance = Balance + 100
    WHERE AccountID = 2;
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
END CATCH;

This ensures that either all changes succeed or none are applied.

XACT_STATE()

XACT_STATE() determines whether the current transaction is usable.

Possible values:

Example:

IF XACT_STATE() = -1
    ROLLBACK TRANSACTION;

Why Use XACT_STATE()?

Some errors leave a transaction in an uncommittable state. Attempting to commit such a transaction will fail.

Example:

BEGIN CATCH
IF XACT_STATE() <> 0
    ROLLBACK TRANSACTION;
END CATCH;

This approach is safer than issuing an unconditional ROLLBACK.

SET XACT_ABORT

SET XACT_ABORT ON automatically rolls back the current transaction when most run-time errors occur.

Example:

SET XACT_ABORT ON;
BEGIN TRANSACTION;
-- Statements
COMMIT;

Benefits:

• Simplifies transaction management.
• Helps avoid partially committed transactions.
• Particularly useful in batch processing.

Logging Errors

A common practice is to log errors to an audit table.

Example:

BEGIN CATCH
INSERT INTO ErrorLog
(
    ErrorNumber,
    ErrorMessage,
    ErrorDate
)
VALUES
(
    ERROR_NUMBER(),
    ERROR_MESSAGE(),
    GETDATE()
);
END CATCH;

Benefits include:

• Simplified troubleshooting.
• Historical analysis.
• Compliance and auditing.

Nested TRY…CATCH Blocks

Complex procedures may use nested error handling.

Example:

BEGIN TRY
    BEGIN TRY
        -- Inner logic
    END TRY
    BEGIN CATCH
        THROW;
    END CATCH;
END TRY
BEGIN CATCH
    -- Outer handling
END CATCH;

Nested blocks allow localized handling while still propagating errors to higher-level logic.

Errors That Cannot Be Caught

Not every SQL Server error is trapped by TRY...CATCH.

Examples include:

• Compile-time syntax errors.
• Certain object resolution errors that occur before execution.
• Severe errors (severity 20 or higher) that terminate the connection.
• Client-side interruptions.

Error Handling Best Practices

• Use TRY...CATCH in stored procedures.
• Prefer THROW over RAISERROR for new development.
• Roll back failed transactions.
• Check XACT_STATE() before committing or rolling back.
• Log important errors.
• Return meaningful messages to calling applications.
• Keep transactions as short as possible.
• Avoid swallowing errors without logging or rethrowing them.
• Use SET XACT_ABORT ON when appropriate for transactional workloads.
• Test error-handling paths, not just successful execution paths.

Common Exam Tips

For the DP-800 exam, remember the following:

• TRY...CATCH is SQL Server’s primary structured error-handling mechanism.
• THROW is the preferred method for raising or rethrowing exceptions.
• RAISERROR is a legacy feature retained for backward compatibility.
• ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_LINE(), ERROR_PROCEDURE(), ERROR_SEVERITY(), and ERROR_STATE() provide detailed error information within a CATCH block.
• Always manage transactions carefully when errors occur.
• Use XACT_STATE() to determine the status of the current transaction.
• SET XACT_ABORT ON automatically rolls back most failed transactions.
• Logging errors improves troubleshooting and operational support.

10 Practice Exam Questions

Question 1

Which T-SQL construct provides structured exception handling?

A. CASE...WHEN

B. TRY...CATCH

C. IF...ELSE

D. WHILE

Answer: B

Explanation: TRY...CATCH is the primary mechanism for structured error handling in SQL Server. Statements in the TRY block execute normally, and any run-time error transfers control to the CATCH block.

Question 2

Which function returns the text description of the error that occurred?

A. ERROR_NUMBER()

B. ERROR_MESSAGE()

C. ERROR_STATE()

D. ERROR_LINE()

Answer: B

Explanation: ERROR_MESSAGE() returns the complete descriptive text associated with the error, making it useful for logging and displaying meaningful messages.

Question 3

Which statement is recommended for raising new user-defined errors in modern SQL Server development?

A. THROW

B. PRINT

C. RETURN

D. GOTO

Answer: A

Explanation: Microsoft recommends using THROW instead of RAISERROR for new development because it provides cleaner syntax and better preserves original error information.

Question 4

What is the purpose of XACT_STATE()?

A. It determines whether indexes are fragmented.

B. It checks whether a transaction is active and whether it can still be committed.

C. It displays the current isolation level.

D. It returns the current database compatibility level.

Answer: B

Explanation: XACT_STATE() returns 1, 0, or -1 to indicate whether a transaction is committable, absent, or uncommittable, respectively.

Question 5

Which value returned by XACT_STATE() indicates an uncommittable transaction?

A. 0

B. 1

C. 100

D. -1

Answer: D

Explanation: A value of -1 indicates that the transaction is active but cannot be committed and must be rolled back.

Question 6

Which function returns the line number where an error occurred?

A. ERROR_PROCEDURE()

B. ERROR_STATE()

C. ERROR_LINE()

D. ERROR_SEVERITY()

Answer: C

Explanation: ERROR_LINE() identifies the line number where the run-time error occurred, making it easier to locate and correct issues.

Question 7

What is the primary benefit of using SET XACT_ABORT ON?

A. It automatically creates savepoints.

B. It automatically commits every transaction.

C. It disables constraint checking.

D. It automatically rolls back most transactions when a run-time error occurs.

Answer: D

Explanation: SET XACT_ABORT ON helps ensure transactional consistency by automatically rolling back the current transaction when most run-time errors occur.

Question 8

Which error information function returns the name of the stored procedure that generated the error?

A. ERROR_PROCEDURE()

B. ERROR_LINE()

C. ERROR_MESSAGE()

D. ERROR_NUMBER()

Answer: A

Explanation: ERROR_PROCEDURE() returns the name of the stored procedure where the error originated, or NULL if the error occurred outside a stored procedure.

Question 9

Which statement about THROW and RAISERROR is correct?

A. RAISERROR is required for all user-defined errors.

B. THROW cannot be used inside a CATCH block.

C. THROW is the recommended approach for new SQL Server applications.

D. THROW does not support custom error messages.

Answer: C

Explanation: THROW is the preferred method for generating and rethrowing exceptions in modern SQL Server development, while RAISERROR is maintained primarily for backward compatibility.

Question 10

Why should transactions typically be rolled back when an error occurs during a multi-step operation?

A. To improve index performance.

B. To reduce memory usage.

C. To prevent SQL Server from generating error messages.

D. To maintain data consistency by ensuring that either all operations succeed or none are applied.

Answer: D

Explanation: Rolling back a failed transaction preserves database consistency by preventing partial updates that could leave related data in an invalid or inconsistent state.

Go to the DP-800 Exam Prep Hub main page