Prev: Investor's Knowledge Analysis
Next: Need Help Deciding On DB
From: Mark B on 3 Jul 2008 23:34
I have written some ASP.NET webservices and some SQL Stored Procedures.

For example, I have a webservice that adds a new employee with 10 input
parameters and 2 output (CounterID and IDKey).

I would like the webservice to return any errors that the stored procedure
encounters when executing.

I am guessing the best method would be to add 2 output parameters to each
sp, namely ReturnedErrorNumber (int) and ReturnedErrorMessage varchar(4000).

Then I could set MyWebserviceMethodFunction(...) As String =
ReturnedErrorNumber (int) & " " & ReturnedErrorMessage varchar(4000)

If it =" " then there was no error.

Questions:

1) Is this a the typical practice to handle errors?

2) How do I get a stored procedure to return an ErrorNumber and
ErrorMessage if there is an error?

TIA

From: Erland Sommarskog on 4 Jul 2008 04:26
Mark B (none(a)none.com) writes:
> I have written some ASP.NET webservices and some SQL Stored Procedures.
>
> For example, I have a webservice that adds a new employee with 10 input
> parameters and 2 output (CounterID and IDKey).
>
> I would like the webservice to return any errors that the stored procedure
> encounters when executing.
>
> I am guessing the best method would be to add 2 output parameters to
> each sp, namely ReturnedErrorNumber (int) and ReturnedErrorMessage
> varchar(4000).
>
> Then I could set MyWebserviceMethodFunction(...) As String =
> ReturnedErrorNumber (int) & " " & ReturnedErrorMessage varchar(4000)
>
> If it =" " then there was no error.
>
> Questions:
>
> 1) Is this a the typical practice to handle errors?

I hardly know what a web service, so I don't know practice may be. But
I think to myself that the return message may not make sense to the
caller.

> 2) How do I get a stored procedure to return an ErrorNumber and
> ErrorMessage if there is an error?

Provided that you are using SQL 2005:

CREATE PROCEDURE my_outer_sp @par1 ... @errno int OUTPUT,
@errmsg nvarchar(MAX) AS
BEGIN TRY
SELECT @errno = 0, @errmsg = NULL
EXEC my_inner_sp @par1, ...
END TRY
BEGIN CATCH
SELECT @errno = error_number(), @errmsg = error_message()
END CATCH

The real meat would be in my_inner_sp. The reason the TRY-CATCH is in an
outer SP, is that some errors cannoy be caught in the same scope.

If you are on SQL 2000, your only option is catch the error in your
ASP .Net code. And I would suggest that this is the best anyway. The
main reason to have TRY-CATCH like this is to be able to log the
error and things like that. Communicating errors through output
variables is not really the custom.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Dan Guzman on 4 Jul 2008 08:45
> Then I could set MyWebserviceMethodFunction(...) As String =
> ReturnedErrorNumber (int) & " " & ReturnedErrorMessage varchar(4000)
>
> If it =" " then there was no error.
>
> Questions:
>
> 1) Is this a the typical practice to handle errors?

To add on to what Erland has said, I think the Best Practice is to catch the
SqlException in the web service code and then throw a SoapException with the
error context that the consumer can handle as desired. An example of this
technique:

http://www.developer.com/net/csharp/article.php/10918_3088231_1

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Mark B" <none(a)none.com> wrote in message
news:%23meMibY3IHA.4448(a)TK2MSFTNGP05.phx.gbl...
>I have written some ASP.NET webservices and some SQL Stored Procedures.
>
> For example, I have a webservice that adds a new employee with 10 input
> parameters and 2 output (CounterID and IDKey).
>
> I would like the webservice to return any errors that the stored procedure
> encounters when executing.
>
> I am guessing the best method would be to add 2 output parameters to each
> sp, namely ReturnedErrorNumber (int) and ReturnedErrorMessage
> varchar(4000).
>
> Then I could set MyWebserviceMethodFunction(...) As String =
> ReturnedErrorNumber (int) & " " & ReturnedErrorMessage varchar(4000)
>
> If it =" " then there was no error.
>
> Questions:
>
> 1) Is this a the typical practice to handle errors?
>
> 2) How do I get a stored procedure to return an ErrorNumber and
> ErrorMessage if there is an error?
>
> TIA

 | 
Pages: 1
Prev: Investor's Knowledge Analysis
Next: Need Help Deciding On DB