Returning Custom Exception Messages from Stored Procedure Calls

Simple Technique to return custom error messages from a stored proc, suitable for populating error labels and the like.

This is something we use all the time, but I've noticed that not everybody does it, so I will reproduce it here. Use the RaiseError(@errMsg, 16,1) command to populate the Exception Message property. Sample stored proc is inline as a comment at the beginning of the sample code:

using System;
using System.Data;
using System.Data.SqlClient ;
namespace TestSqlError
{
/*
CREATE PROC TESTERROR
AS
Declare @sMsg varchar(500)
Set @sMsg='This is the error!'
raiserror(@sMsg,16,1)
*/
class Tester
{
[STAThread]
static void Main(string[] args)
{
SqlConnection cn = new SqlConnection("server=.;database=Northwind;uid=sa;pwd=;");
SqlCommand cmd = new SqlCommand("TESTERROR",cn);
cmd.CommandType=CommandType.StoredProcedure ;
cn.Open();
try
{
cmd.ExecuteNonQuery();
}
catch(Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
cn.Close();
cmd.Dispose();
}
Console.ReadLine();
}
}
}


Submission Date:  5/18/2006 3:40:30 PM
Submitted By:  Peter Bromberg
My Home Page:  http://www.eggheadcafe.com