How to raise an error on an SP and catch the exception in code
Some times, especially when working in a transactional environment, we wound need to raise an exception from our SQL server Stored Procedure deliberately and catch this exception, for example, in a c# application within a try catch block in order to Roll Back the changes.
This is common behaviour when having the .NET code manage the transaction logic, when the code will call several Stored Procedures and the Roll Back is coordinated in code.
so our call to the DB will look something like this:
using (SqlDB db = new SqlDB(BusinessLogic.Constants.ConnectString))
{
try
{
SqlParameter p;
SqlCommand cmd;
using (cmd = new SqlCommand("usp_MyUsp", db.Connection, db.Transaction))
{
p = new SqlParameter("@id", SqlDbType.NVarChar, 50); p.Value = data.id; cmd.Parameters.Add(p);
p = new SqlParameter("@data1", SqlDbType.NVarChar, 40); p.Value = data.data1; cmd.Parameters.Add(p);
p = new SqlParameter("@data1", SqlDbType.Int); p.Value = data.data1; cmd.Parameters.Add(p);
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteReader()
}
db.Commit();
}
catch (Exception ex)
{
db.Rollback();
}
}
To raise an error in the SP that will be caught in the catch block we need to call the SQL server method RAISERROR() and immediately return a value. the Stored Procedure to raise the error would look something like this:
DECLARE @DocID int;
--find the id
SELECT @DocID = id FROM table WHERE id = @id
--if not exist send back an error
IF @DocID is null
BEGIN
RAISERROR('ID not Found', 16, 1)
RETURN -1
END
note: concatenating the error message might create an error.
This example is very general, the two numbered parameters passed in to the method in this case (16 ,1) represent the severity and status of the raised error.
for more detailed information you can read it on the MSDN pages. http://msdn.microsoft.com/en-us/library/ms178592.aspx
Subscribe