How to Pass Back Error Messages 1

[tadd]

I have a web page that calls a web service that calls a stored procedure. I want to elegantly handle any errors that might occur in the stored procedure so that the user has some feedback.

Can someone recommend a good way to pass error messages all the way from the stored procedure back up to the web page that called the webservice? I'm new to this.

Thanks for any tips...

 

[ptheriault]

If this is for SQL Server 2005 you can use this method

BEGIN TRY

your code here

END TRY
BEGIN CATCH 
  IF @@TRANCOUNT > 0
     ROLLBACK

  DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
  SELECT @ErrMsg = ERROR_MESSAGE(),
         @ErrSeverity = ERROR_SEVERITY()

  RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH

- Paul
- Database performance looks fine, it must be the Network!

 

[tadd]

ok...hope this is not a dumb question but...how do those error messages get passed along to the webservice...and then to the web page that calls the webservice?

 

[ptheriault]

Not a dumb question... I'm not sure of the exact syntax since I'm a DBA and not a programmer but you have to code the application to look for the returned error. Maybe George or Denis can give you the syntax for that.

- Paul
- Database performance looks fine, it must be the Network!

 

[philhege]

If your function expects a dataset in return, you could put the message in a field and evaluate the recordset fields when it returns to the app:

SELECT @@ERROR_MESSAGE() as error_text,ERROR_SEVERITY()
as severity

And in the app:
...whatever you use to load the recordset (rs)...

IF rs.Columns(0).ColumnName = "error_text" THEN
'process the error
ELSE
'process normally

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.

 

[tadd]

Fantastic. I'll give that a try. Thanks a lot...