SQL Server Exception Handling Part 1


This series is based on the series of articles on sqlhints.com under the topic of exception handling.

In a previous post called SQL Server Exception handling Template, I show the code from sqlhints.com that is a template that you can use in your code that may be the best solution for handling errors in SQL Server.

The purpose of this post series is to go back through more simple examples to understand why the above mentioned template may be your best solution.

Error Message

Here is an example.

SELECT * FROM dbo.NonExistingTable
GO

Here is the error message in the Results pane of SQL Server Management Studio.

Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.NonExistingTable'.

The error message has five parts.

  • Msg 208 – Error Number
  • Level 16 – Severity of the Error
  • State 1 – State of the Error
  • Line 2 – Line Number of the statement which generated the Error
  • Invalid object name ‘dbo.NonExistingTable’. – Actual Error Message

Thanks to sqlhints.com, the below image explains in detail each of the six parts of the error message which we have identified just above:

The following code will create a stored procedure, but will also display an error since the table does not exist in the database.

CREATE PROCEDURE dbo.ErrorMessageDemo
AS
BEGIN
    SELECT * FROM dbo.NonExistingTable
END
GO
--Execute the Stored Procedure
EXEC dbo.ErrorMessageDemo
GO

The error is as follows.

Msg 208, Level 16, State 1, Procedure ErrorMessageDemo, Line 4 [Batch Start Line 6]
Invalid object name 'dbo.NonExistingTable'.

The next post will discuss how SQL Server reacts to different actions.

Series Navigation<< SQL Server Exception Handling IntroductionSQL Server Exception Handling Part 2 >>

Leave a comment

Your email address will not be published. Required fields are marked *