Master SQL Server From Scratch

Clear, interactive, and structured SQL Server lessons designed for beginners and developers.

COURSE SYLLABUS
1. Introduction to SQL Server
Introduction to SQL Server SQL Server Installation SQL Server Fundamentals
2. Data Types
Data Types Numeric Data Types Character & String Data Types Date & Time Data Types Other SQL Server Data Types
3. Database & Tables
Database & Tables Introduction Creating a Database Creating Tables Table Structure Altering Tables Dropping Tables
4. Constraints
Constraints Introduction PRIMARY KEY FOREIGN KEY UNIQUE Constraint NOT NULL & DEFAULT CHECK Constraint
5. SQL Basics
SQL Basics Introduction SQL Commands SQL Command Categories SELECT Statement INSERT, UPDATE & DELETE
6. CRUD Operations
CRUD Operations Introduction INSERT Operation SELECT Operation UPDATE Operation DELETE Operation
7. SQL Queries
Queries Introduction WHERE Clause ORDER BY Clause DISTINCT LIKE Operator IN, BETWEEN & IS NULL
8. Joins
Joins Introduction INNER JOIN LEFT JOIN RIGHT JOIN FULL OUTER JOIN CROSS JOIN
9. SQL Functions
SQL Functions Introduction String Functions Numeric Functions Date & Time Functions Conversion Functions NULL Functions
10. Grouping & Aggregate Functions
GROUP BY Aggregate Functions COUNT() SUM() AVG() MIN() & MAX()
11. Subqueries
Subqueries Introduction Single-Row Subquery Multiple-Row Subquery Correlated Subquery Subquery with DML
12. Views
SQL Views Introduction Creating a View Updating a View Altering a View Dropping a View
13. Stored Procedures
Stored Procedures Introduction Creating a Stored Procedure Input Parameters Output Parameters Executing & Modifying Stored Procedures
14. Triggers
Triggers Introduction AFTER INSERT Trigger AFTER UPDATE Trigger AFTER DELETE Trigger INSTEAD OF Trigger
15. Transactions
Transactions Introduction COMMIT ROLLBACK SAVEPOINT Transaction with TRY...CATCH
16. Indexes
Indexes Introduction Clustered Index Non-Clustered Index Creating & Dropping Indexes Index Performance
17. Common Table Expressions
CTE Introduction Simple CTE CTE with JOIN CTE with Aggregate Functions Recursive CTE
18. Window Functions
Window Functions Introduction ROW_NUMBER() RANK() and DENSE_RANK() LEAD() and LAG() PARTITION BY
19. Advanced SQL
Advanced SQL Introduction Temporary Tables Table Variables Dynamic SQL Error Handling Performance Optimization

Error Handling in SQL Server

Learn how to detect, handle and manage errors in SQL Server using TRY...CATCH, error functions, THROW and RAISERROR.

1. What is Error Handling?

Error Handling is the process of detecting and managing errors that occur during the execution of SQL statements.

Without proper error handling, an unexpected error can stop the execution of a SQL batch, stored procedure or transaction.

SQL Server provides the TRY...CATCH mechanism to handle runtime errors.

2. Why is Error Handling Important?

  • It prevents unexpected termination of SQL operations.
  • It provides useful information about errors.
  • It allows applications to respond properly to errors.
  • It helps maintain database consistency.
  • It is especially important when working with transactions.

3. TRY...CATCH

SQL Server uses the TRY...CATCH construct to handle runtime errors.

Statements that may generate an error are placed inside the TRY block.

If an error occurs, SQL Server transfers control to the CATCH block.

BEGIN TRY

    -- SQL statements

END TRY
BEGIN CATCH

    -- Error handling statements

END CATCH;

4. Simple TRY...CATCH Example

The following example generates a divide-by-zero error. The error is handled by the CATCH block.

BEGIN TRY

    SELECT 10 / 0 AS Result;

END TRY
BEGIN CATCH

    PRINT 'An error occurred.';

END CATCH;
Explanation:
  • TRY contains the statement that may generate an error.
  • 10 / 0 generates a divide-by-zero error.
  • Control moves to the CATCH block.
  • The error message is printed.

5. ERROR_NUMBER()

ERROR_NUMBER() returns the error number generated by SQL Server.

BEGIN TRY

    SELECT 10 / 0;

END TRY
BEGIN CATCH

    SELECT ERROR_NUMBER() AS ErrorNumber;

END CATCH;

6. ERROR_MESSAGE()

ERROR_MESSAGE() returns the complete error message generated by SQL Server.

BEGIN TRY

    SELECT 10 / 0;

END TRY
BEGIN CATCH

    SELECT ERROR_MESSAGE() AS ErrorMessage;

END CATCH;

7. ERROR_SEVERITY()

ERROR_SEVERITY() returns the severity level of the error.

BEGIN TRY

    SELECT 10 / 0;

END TRY
BEGIN CATCH

    SELECT ERROR_SEVERITY() AS ErrorSeverity;

END CATCH;

8. ERROR_STATE()

ERROR_STATE() returns the state number associated with the error.

BEGIN TRY

    SELECT 10 / 0;

END TRY
BEGIN CATCH

    SELECT ERROR_STATE() AS ErrorState;

END CATCH;

9. ERROR_LINE()

ERROR_LINE() returns the line number at which the error occurred.

BEGIN TRY

    SELECT 10 / 0;

END TRY
BEGIN CATCH

    SELECT ERROR_LINE() AS ErrorLine;

END CATCH;

10. ERROR_PROCEDURE()

ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.

BEGIN TRY

    SELECT 10 / 0;

END TRY
BEGIN CATCH

    SELECT ERROR_PROCEDURE() AS ErrorProcedure;

END CATCH;

11. Retrieve Complete Error Information

We can use multiple error functions together to retrieve detailed information about an error.

BEGIN TRY

    SELECT 10 / 0;

END TRY
BEGIN CATCH

    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine;

END CATCH;

12. THROW Statement

The THROW statement can be used to generate an exception or re-throw an existing error.

It is commonly used inside a CATCH block when the application needs to pass the error to another layer.

BEGIN TRY

    SELECT 10 / 0;

END TRY
BEGIN CATCH

    THROW;

END CATCH;

13. Creating a Custom Error using THROW

THROW 50001,
      'Employee salary cannot be negative.',
      1;

User-defined error numbers used with THROW should be greater than or equal to 50000.

14. RAISERROR

RAISERROR can be used to generate a custom error message.

RAISERROR(
    'Something went wrong.',
    16,
    1
);
Note: For newer SQL Server code, THROW is generally preferred for raising and re-throwing errors.

15. Error Handling with Transactions

TRY...CATCH is very important when working with database transactions.

If an error occurs after a transaction starts, the transaction can be rolled back.

BEGIN TRY

    BEGIN TRANSACTION;

    UPDATE Employees
    SET Salary = Salary + 1000
    WHERE EmployeeId = 1;

    COMMIT TRANSACTION;

END TRY
BEGIN CATCH

    IF @TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    SELECT ERROR_MESSAGE() AS ErrorMessage;

END CATCH;

16. Practical Error Handling Example

The following example demonstrates a complete error handling pattern.

BEGIN TRY

    BEGIN TRANSACTION;

    UPDATE Employees
    SET Salary = Salary + 5000
    WHERE EmployeeId = 101;

    IF @ROWCOUNT = 0
    BEGIN
        THROW 50001,
              'Employee was not found.',
              1;
    END;

    COMMIT TRANSACTION;

END TRY
BEGIN CATCH

    IF @TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage,
        ERROR_LINE() AS ErrorLine;

END CATCH;

Important Points

  • Use TRY...CATCH to handle runtime errors.
  • Use ERROR_MESSAGE() to get the error message.
  • Use ERROR_NUMBER() to get the error number.
  • Use ERROR_LINE() to identify the error line.
  • Use ERROR_PROCEDURE() to identify the procedure or trigger.
  • Use THROW to generate or re-throw errors.
  • When using transactions, check @TRANCOUNT before rolling back.
  • Proper error handling helps maintain database consistency.

Summary

Error Handling provides a structured way to manage errors in SQL Server.

The TRY...CATCH mechanism allows SQL Server applications to detect and handle runtime errors.

Functions such as ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_LINE() and ERROR_PROCEDURE() provide useful error information.