How can we display an error message coming from the stored proc ?

Question

How can we display an error message coming from the stored proc ?

mw_sql_developer

SSCoach

Points: 19445
More actions
January 18, 2012 at 7:57 am

#391338

I have an SSRS Report that uses a stored proc.
Inside my stored proc,
I will be checking for input parameters passed by user and then I may want to display an message back to the user
saying "Sorry no records found." or " Error: User entered an invalid date."
How do I send a message back so that it gets displayed back to the user.
What I don't want is to have an alert box with a "OK" and "CANCEL" button which is annoying to the user.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 1

If you just wanted to return a no rows found there is a property in a report called "NoRowsMessage" that is designed for this purpose, but it will not relay errors or messages from an SP.

If you want to feed back a message from the SP, I would code the SP to return your message in a single field. Then in the report have the report look for a specific return keyword (Error or something like that) so that you can do some conditional formatting on the report such as remove unwanted columns, etc. and then display the message.

mw_sql_developer SSCoach Points: 19445 More actions · Answer 2

I have noticed if something goes wrong inside the stored proc while running, SSRS will display the

ugly error information. Well, if that can happen i am sure there is a way for us to throw personalized messages back to the user ? What do you think ?

Phil Parkin SSC Guru Points: 247180 More actions · Answer 3

Does the stored proc include Try ... Catch and RaiseError error trapping?

If not, you could try implementing that and see if it does what you wish.

mw_sql_developer SSCoach Points: 19445 More actions · Answer 4

Wonderful idea let me try that

I need to get familiar with the raiseerror stmt. Any help with syntax

Phil Parkin SSC Guru Points: 247180 More actions · Answer 5

raiserror('Your master data table has been truncated. Please have a quiet chat with your DBA', 1,1) with nowait

Read BOL to make sure you understand the concepts behind the arguments and decide whether NOWAIT is right for you.

mw_sql_developer SSCoach Points: 19445 More actions · Answer 6

It worked thanks

The RAISEERROR has to go inside the catch block. This is the error message I get(below).

The only thing I don't like is can this message be customized ?

I only included the following stmt in my code ?

RAISERROR ('Error: Reporting Server is down at the moment. Please try later', -- Message text.

16, -- Severity.

1 -- State.

);

So my question is: Is there a way to customize the error message ? ( Take away that part that says "Query execution failed for data set 'Report_Data'." ) ?????

An error has occurred during report processing.

Query execution failed for data set 'Report_Data'.

Error: Reporting Server is down at the moment. Please try later