.Net application development specialists
asp.net, c#, vb.net, html, javascript, jquery, html, xhtml, css, oop, design patterns, sql server, mvc and much more
contact: admin@paxium.co.uk

Paxium is the company owned by myself, Dave Amour and used for providing IT contract development services including


  • Application development - Desktop, Web, Services - with Classic ASP, Asp.net WebForms, Asp.net MVC, Asp.net Core
  • Html, Css, JavaScript, jQuery, React, C#, SQL Server, Ado.net, Entity Framework, NHibernate, TDD, WebApi, GIT, IIS
  • Database schema design, implementation & ETL activities
  • Website design and hosting including email hosting
  • Training - typically one to one sessions
  • Reverse Engineering and documentation of undocumented systems
  • Code Reviews
  • Performance Tuning
  • Located in Cannock, Staffordshire
Rugeley Chess Club Buying Butler Cuckooland Katmaid Pet Sitting Services Roland Garros 60 60 Golf cement Technical Conformity Goofy MaggieBears Vacc Track Find Your Smart Phone eBate Taylors Poultry Services Lafarge Rebates System Codemasters Grid Game eBate DOFF

Using RAISERROR in SQL Server

When working with SQL Server as a data source in your applications you really should be working with stored procedures and when working with stored procedures there are a few things you need to be able to do and work with.

The things which this article is going to focus on are responding to errors in your client code, raising errors yourself within your stored procedures and working with transactions.

This article will take a reasonably in depth look at these topics and will hopefully give you an insight into how these things work and give you some practical tips and samples you can use and learn from.

Ok so we are going to be working with Visual Studio 2008 and SQL Server 2005 but any reasonable configuration of Visual Studio and SQL Server will probably suffice.

So first create a new console project in Visual Studio with C# as the language and call this project something sensible.

Next we will need a database so create one if you want to use a brand new one, or like me you might have a generic database which you use for general tinkering or use that if that is easier for you, doesn't really matter.

So now we will need a table to work with for our tests.  Create one called tbl_People as follows:

USE [YourDatabaseNameGoesHere]
/****** Object: Table [dbo].[tbl_People]    Script Date: 04/02/2009 10:07:17 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
CREATE TABLE [dbo].[tbl_People](
      [PersonID] [int] IDENTITY(1,1) NOT NULL,
      [Firstname] [varchar](50) NOT NULL,
      [Surname] [varchar](50) NOT NULL,
      [Age] [int] NOT NULL,
 CONSTRAINT [PK_tbl_People] PRIMARY KEY CLUSTERED
(
      [PersonID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
SET ANSI_PADDING OFF


Ok so now lets open up a new query window in SQL Server Management Studio and let's run a few bits of SQL

First let's insert a record as follows:

Insert Into tbl_People (Firstname, Surname, Age) Values ('Fred', 'Williams', 33)


This should work fine and we should now have one record in our table. We are now going to run a few sql statements in a deliberate attempt to force errors to occur.  First run the following statement: 

Insert Into tbl_People (Firstname, Surname, Age) Values ('Tiffany', 'Smith', Null)
Insert Into tbl_People (Firstname, Surname, Age) Values ('Dave', 'Amour', 42)
Insert Into tbl_People (Firstname, Surname, Age) Values ('Sue', 'Smith', 23)


We should then see the following message output:

Msg 515, Level 16, State 2, Procedure Test, Line 5
Cannot insert the value NULL into column 'Age', table 'DacServices.dbo.tbl_People'; column does not allow nulls. INSERT fails.
The statement has been terminated.
 
(1 row(s) affected)
 
(1 row(s) affected)


So in this case we can see that this particular error doesn't halt the script dead in it's tracks but instead carries on and attempts to execute any other statements in the script.  This is called a non fatal error.

Lets have another and introduce a more serious error.  Change the first insert in the above script so that it looks like this:

Insert Into tbl_PeopleXXXX (Firstname, Surname, Age) Values ('Tiffany', 'Smith', 33)


Now it is obvious that this won't work as there is no table called tbl_PeopleXXXX unless you are mad of course and just happen to have one called that! So run the script again and this time we will see the following message output:

Msg 208, Level 16, State 1, Procedure Test, Line 5
Invalid object name 'tbl_PeopleXXXX'.


This time then we can see that this error was considered severe enough that no further statements in the script should be executed.  This type of error is called a fatal error since it halts execution of the script or stored prcoedure unlike the non fatal error which carried on and ran the subsequent statements.

Also now we have had a look at a couple of different error messages we can see that they are built up of smaller bits of information.  Each error message has a Msg number - eg 208 for the invalid object and 515 for not being able to insert null.  We also see that the error messages have a Level and a State number.  They also have a bit of text telling us the name of the procedure being executed, the line number the error occurred on and finally the error message text.

So where does the data for these error messages come from?  Well like most things in SQL Server they reside in a databse table.  Specifically the sysmessages table in the master database.

If you execute the following sql:

Use Master
 
Select * From SysMessages


Then you should see something like this:

Ok so lots of these errors are raised by SQL Server when we try and doing something bad but what if we want to raise our own errors?  We might have a stored procedure which takes an ID as a parameter and just returns one record from our People table we created earlier.  We could raise errors if the ID passed in was less than 1 or if there is no matching record for the given ID.  This is just a contrived scenario for raising errors and may not be the most realistic one but for the purposes of this article I really wanted a stored prcoedure which raises 2 different errors so we will with that.

Create a new stored procedure then as follows:

USE [DacServices]
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
ALTER Procedure [dbo].[sp_GetRecord]
 
@PersonID Int
 
As
 
If @PersonID < 1
Begin
      RAISERROR ('Cannot have a PersonID < 1', 16, 1)
End
 
Else If Exists (Select * From tbl_People Where PersonID = @PersonID)
Begin
      Select * From tbl_People Where PersonID = @PersonID
End
 
Else
 
Begin
      RAISERROR ('Cannot find a record with that PersonID', 16, 1)
End


Ok so this shouldn't be too difficult to follow.  SQL server has a built in facility for raising errors called RAISERROR (Just 1 e!) and we can use that with three parameters for raising custom errors.  The parameters are the error message, then a severity number and then a state number.

We will come back to what these last two parameters mean shortly but first let's write some .net code to consume this procedure.

So now we will back to the console project we created earlier in Visual Studio and we will add a method to this called DisplayRecord.  This method will take an int as a parameter and will try and display the record with that ID from our database.  We will call that a few times with some valid and invalid values and see how we can deal with the errors.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
 
namespace SQLErrors
{
    class Program
    {
        static void Main(string[] args)
        {
            DisplayRecord(2);
            DisplayRecord(33);
            DisplayRecord(-2);
            DisplayRecord(4);
 
            Console.Read();
        }
 
        static void DisplayRecord(int personID)
        {
            string connectionString = "server=192.168.1.188; database=dacservices; uid=myuserID; pwd=mypassword”;
 
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand command = new SqlCommand("sp_GetRecord", conn))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add("@PersonID", SqlDbType.Int);
                    command.Parameters["@PersonID"].Value = personID;
                                       
                    conn.Open();
 
                    try
                    {
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            reader.Read();
 
                            Console.WriteLine("PersonID: {0}", reader.GetInt32(0));
                            Console.WriteLine("Name: {0} {1}", reader.GetString(1), reader.GetString(2));
                            Console.WriteLine("Age: {0}", reader.GetInt32(3));
                            Console.WriteLine();
                        }
                    }
                    catch (SqlException ex)
                    {
                        Console.WriteLine(ex.Message + Environment.NewLine);
                    }
                }
            }
        }
    }
}


Ok so if you have everything correct here including your connection string then this should all work and you should see output simillar to that below. You will of course have to poplulate your table with some test data and tinker with the values passed in to the DisplayRecord method accordingly

Ok so this is good if all we want to do is catch an error and display the text of the error message.  What if instead we wanted to catch the two different types of errors and get our code to react differently depending on what error was raised?  How could we do that?  Well we could programmatically examine the text of the error mesage and do it that way but that is very clunky and unprofessional.

A better way would be if we could somehow inspect the error number which is received from SQL Server.

If we put a break point in our code on the line inside the catch block of our try catch statement we will be able to inspect the Exception object and see if the error number is available.  Doing so should show you something like this:

 

Ok so that's great - we can see the error number is 50000.  We can also see the severity is in there too but is called Class for some reason?  And we can see other useful bits of information too such as the line number.  So lets modify our code initially so we get the error number output to the screen too.

Change your DisplayRecord method so that it now looks like this:

        static void DisplayRecord2(int personID)
        {
            string connectionString = "server=192.168.1.188; database=dacservices; uid=sa; pwd=manver1967!?";
 
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand command = new SqlCommand("sp_GetRecord", conn))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add("@PersonID", SqlDbType.Int);
                    command.Parameters["@PersonID"].Value = personID;
 
                    conn.Open();
 
                    try
                    {
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            reader.Read();
 
                            Console.WriteLine("PersonID: {0}", reader.GetInt32(0));
                            Console.WriteLine("Name: {0} {1}", reader.GetString(1), reader.GetString(2));
                            Console.WriteLine("Age: {0}", reader.GetInt32(3));
                            Console.WriteLine();
                        }
                    }
                    catch (SqlException ex)
                    {
                        Console.WriteLine("(" + ex.Number.ToString() + ") " + ex.Message + Environment.NewLine);
                    }
                }
            }
        }


If you now run this after your changes you should see that the error number is displayed too.  But wait, both error numbers have a value of 50000 even though they are different errors!  So what is happening?  Well all that is happening is that when you use RAISERROR to raise an adhoc error message then SQL Server will always give it a value of 50000.  So how can we give our error message different and unique numbers?  To do that we need to add our custom messages to the sysmessages table and then use RAISERROR slightly differently so that it gets the error message from the sysmessage table instead and then it will also get the unique error message number.

So how do we add our custom messages to the sysmessages table?  Well in earlier versions of SQL Server it was possible to directly edit system tables if you configured the server that way.  See the following screenshot from SQL Server 2000 for example.

Even thought it was possible to do this in SQL Server 2000, it was preferable to use a system stored procedure called sp_addmessage which is a lot safer.  In versions of SQL Server after 2000 I think this feature is not available anyway so we have to use sp_addmessage.

So we will add a new custom message and the next free message number should in theory be 50001.  You need to check this though as there may already be other custom messages already defined.  Also consider how you might deploy an application which used such custom messages.  You would need installation code/scripts which inserted custom messages using available message numbers and then writing these somewhere else within the application configuration so that the .net code could match them up with the SQL Server ones.  You would also as part of your installation need to modify any of your stored procedures which raised such errors to use the right error numbers if others already existed forcing us to use different numbers.

The deployment of such an application is beyond the scope of this article though but that just gives you food for thought should you get that far.

Ok so now lets add a test message as follows:

sp_addmessage 50001, 16, 'This is a test error mesage'
 

And now we can test raising this as follows: 

RAISERROR(50001, 16, 1)
 

Now the weird thing here is that the RAISERROR function takes three parameters.  The first can be either a text error message as we used earlier, or it can be an error number in which case the error text is looked up in the sysmessages table.  However we have to supply the other two parameters.  The first one being the severity and the second one being state.  Now the severityy is actually stored in the sysmessages table and yet we still have to manually add it in our function call.  I have researched this and it is simply how it is, even though it makes no sense, thats just the way the system works.  I'm sure this is just an oversight by Microsoft but we have to live with it and it's not such a big deal really as long as we understand why it works this way.

Now lets have a quick look at the severity.  Numbers from 1-10 are warnings.  11-16 are errors and 17-25 are severe errors.  Any severe errors will halt script execution immediatley.  Errors with a severity 16 or below may or may not halt script execution.  There doesn't seem to be any hard or fast rule here.  This of course only applies to system errors.  The errors we use are handled by us and our code so we can react to them how we see fit.

Regarding the state number, there is little documentation on that so generally just set that to 1 but I suspect it can be used to raise the same error but with additional variations eg:

RAISERROR(50001, 16, 1)
RAISERROR(50001, 16, 2)
RAISERROR(50001, 16, 3)


So in this way we could raise the same general error but with variations.  Generally though you probably won't use this so just set it to 1.

So now lets remove this test mesage from the sysmessages table and then add our two messages which we used earlier.  The SQL for this is as follows:

sp_dropmessage 50001
 
sp_addmessage 50001, 16, 'Cannot have a PersonID < 1'
 
sp_addmessage 50002, 16, 'Cannot find a record with that PersonID'


We now also need to modify our stored procedure to raise these custom errors. The modified procedure should look like this:

USE [DacServices]
 
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
ALTER Procedure [dbo].[sp_GetRecord]
 
@PersonID Int
 
As
 
If @PersonID < 1
Begin
      RAISERROR (50001, 16, 1)
End
 
Else If Exists (Select * From tbl_People Where PersonID = @PersonID)
Begin
      Select * From tbl_People Where PersonID = @PersonID
End
 
Else
 
Begin
      RAISERROR (50002, 16, 1)
End


And now if we run our console application again we should see the correct error numbers as follows:

And then our .net code can identify errors by their error number rather than by some clunky code looking at error message text.

We could even create .net enumerations to map to these error messages which would work great.

So thats as much as we are going to look at for raising errors other than to mention how we might user errors along with transactions.  This article is getting a bit lengthy so transactions will be dealt with in a seperate article which you will soon be able to find here:

http://www.audacs.co.uk/ViewPage.aspx?PageID=499

I should also like to thank Tim Chapman who helped with some of the finer details in this article.  You can find Tim's site on SQL Server at:

http://sqlservernation.com