Catch Error and Rethrow with sensible message

SQL Server 2005 / C#.NET
Hi I have a stored procedure in SQL Server 2005 with Error Handling... Lets say user passes CustomerID Number 2 to be deleted. Then I will get a Foreign key violation in RaiseError. Now i want to Catch that Error message Format it and send back to my client from my Class...  How can I format the Original message being thrown, Modify it and rethrow it back again.

Create Procedure Delete_Customer
@Cust_ID int
As
BEGIN TRY
Delete from Customer where customer_ID =@Cust_ID
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
SELECT @ErrorMessage = ERROR_MESSAGE();
RAISERROR (@ErrorMessage, 16, 1);
END CATCH
dotnet0824Asked:
Who is Participating?
 
imitchieCommented:
Create Procedure Delete_Customer
@Cust_ID int
As
BEGIN TRY
Delete from Customer where customer_ID =@Cust_ID
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000)

SELECT @ErrorMessage =
'There was a problem trying to delete customer ['
+ convert(varchar, @Cust_ID) + ']' + Char(13) + Char(10) +
'The error was: ' + Char(13) + Char(10) +
ERROR_MESSAGE()

RAISERROR (@ErrorMessage, 16, 1)
END CATCH
0
 
RedKelvinCommented:
Hi there, try this

            try
            {
                int x;
                int y = 0;
                x = 1 / y;
            }
            catch (DivideByZeroException ex)
            {
                throw new DivideByZeroException("My custom message " + ex.Message);
            }

Open in new window

0
 
dotnet0824Author Commented:
Whatz that.. did u read my question
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
RedKelvinCommented:
yes I did read your question, you want to catch an error and rethrow it with a custom message, if you read my code, you will see that is what is happening, you can ignore the divide by zero, I was just using that so that you could see that it is working
0
 
multithreadingCommented:
Given that you are on Sql 2005, you could use .Net for your procedure. Then you could have complete control over the try/catch. It would be more work than the "i wish it were like this" psuedo code you wrote, but after registering the DLL in SQL 2005 it would get you were you are trying to go.
0
 
dotnet0824Author Commented:
can we use Varchar(MAX) instead of DECLARE @ErrorMessage NVARCHAR(4000)
0
 
dotnet0824Author Commented:
RedKelvin:yes I did read your question, you want to catch an error and rethrow it with a custom message, if you read my code, you will see that is what is happening, you can ignore the divide by zero, I was just using that so that you could see that it is working

You were saying about DivideByZero exception...being caught @ client. But in my case how can I use the same technique @ the client.....
0
 
imitchieCommented:
yes varchar(max) will be fine for most standard installations. although i can't imagine you wanting to stuff more than 4000 bytes as an error message.  nvarchar is good for multi-byte system, but i don't think that applies to you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.