Solved

Catch Error and Rethrow with sensible message

Posted on 2007-11-18
8
569 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
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 22

Expert Comment

by:RedKelvin
Comment Utility
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
Comment Utility
Whatz that.. did u read my question
0
 
LVL 22

Expert Comment

by:RedKelvin
Comment Utility
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
 
LVL 7

Expert Comment

by:multithreading
Comment Utility
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 25

Accepted Solution

by:
imitchie earned 500 total points
Comment Utility
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
Comment Utility
can we use Varchar(MAX) instead of DECLARE @ErrorMessage NVARCHAR(4000)
0
 

Author Comment

by:dotnet0824
Comment Utility
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
Comment Utility
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now