Solved

Catch Error and Rethrow with sensible message

Posted on 2007-11-18
8
575 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:dotnet0824
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 22

Expert Comment

by:RedKelvin
ID: 20309558
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
 

Author Comment

by:dotnet0824
ID: 20309597
Whatz that.. did u read my question
0
 
LVL 22

Expert Comment

by:RedKelvin
ID: 20309685
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 7

Expert Comment

by:multithreading
ID: 20309806
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
 
LVL 25

Accepted Solution

by:
imitchie earned 500 total points
ID: 20309922
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
 

Author Comment

by:dotnet0824
ID: 20310890
can we use Varchar(MAX) instead of DECLARE @ErrorMessage NVARCHAR(4000)
0
 

Author Comment

by:dotnet0824
ID: 20310898
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
 
LVL 25

Expert Comment

by:imitchie
ID: 20311124
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

Featured Post

Veeam gives away 10 full conference passes

Veeam is a VMworld 2017 US & Europe Platinum Sponsor. Enter the raffle to get the full conference pass. Pass includes the admission to all general and breakout sessions, VMware Hands-On Labs, Solutions Exchange, exclusive giveaways and the great VMworld Customer Appreciation Part

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question