Hi

I'm having difficulty understanding how exceptions/errors are
propagated up the SP call stack. Does SQL server have such a thing as
exception bubbling up ?

My usage scenario is this. I have got many SPs (including SPs calling
other SPs etc) and in any one of them, a state may be detected which
should throw the respective states' (common) exception. The exception
should bubble up the SP call stack and finally be displayed to the
user. Having IF-RAISERROR-RETURN in multiple SPs for the same
condition/error seems redundant work. Is there another of doing this?

Pls advice.

Riyaz

Hi
This is for nested cases where sp calls another sp. You check the error
status and behaive respectively

CREATE PROCEDURE BigOne
AS
DECLARE @err integer
BEGIN TRANSACTION
EXEC @err = sp1
SELECT @err = coalesce(nullif(@err, 0, @@error)
IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
EXEC @err = sp2
SELECT @err = coalesce(nullif(@err, 0, @@error)
IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
EXEC @err = sp3
SELECT @err = coalesce(nullif(@err, 0, @@error)
IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
EXEC @err = sp4
SELECT @err = coalesce(nullif(@err, 0, @@error)
IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
COMMIT TRANSACION
GO



"Riyaz Mansoor" <riyaz.mansoor@gmail.com> wrote in message
news:5140d6f0-cb7c-4a6b-ad7a-cb218da58b75@w7g2000hsa.googlegroups.com...
>
> Hi
>
> I'm having difficulty understanding how exceptions/errors are
> propagated up the SP call stack. Does SQL server have such a thing as
> exception bubbling up ?
>
> My usage scenario is this. I have got many SPs (including SPs calling
> other SPs etc) and in any one of them, a state may be detected which
> should throw the respective states' (common) exception. The exception
> should bubble up the SP call stack and finally be displayed to the
> user. Having IF-RAISERROR-RETURN in multiple SPs for the same
> condition/error seems redundant work. Is there another of doing this?
>
> Pls advice.
>
> Riyaz

Check out Erland's error handling articles at http://www.sommarskog.se/.

--
Hope this s.

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

"Riyaz Mansoor" <riyaz.mansoor@gmail.com> wrote in message
news:5140d6f0-cb7c-4a6b-ad7a-cb218da58b75@w7g2000hsa.googlegroups.com...
>
> Hi
>
> I'm having difficulty understanding how exceptions/errors are
> propagated up the SP call stack. Does SQL server have such a thing as
> exception bubbling up ?
>
> My usage scenario is this. I have got many SPs (including SPs calling
> other SPs etc) and in any one of them, a state may be detected which
> should throw the respective states' (common) exception. The exception
> should bubble up the SP call stack and finally be displayed to the
> user. Having IF-RAISERROR-RETURN in multiple SPs for the same
> condition/error seems redundant work. Is there another of doing this?
>
> Pls advice.
>
> Riyaz

Hi.

I've taken a look at http://www.sommarskog.se/. These articles are
mainly geared towards v2000 and just about touches v2005. Anyways,
what I was looking at was batch-abortion - ie execution stops,
transactions are rolled back and error raised to the client.

Can I force raise batch-abortion exceptions? This would solve my
immediate requirements (I think)

Riyaz

> Can I force raise batch-abortion exceptions? This would solve my
> immediate requirements (I think)

Although a kludge, you can do something like:

SET XACT_ABORT ON
IF <some error condition>
BEGIN
RAISERROR('An error is detected',16,1)
SELECT 1/0 --this will abort the batch due to XACT_ABORT ON
END

FYI, there is a Connect feedback item to allow RAISERROR to abort the batch:
http://connect.microsoft.com/SQLServ...dbackID=275308


--
Hope this s.

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

"Riyaz Mansoor" <riyaz.mansoor@gmail.com> wrote in message
news:a7f574ed-2ff7-4434-b2a5-9f3d6623b366@l42g2000hsc.googlegroups.com...
>
> Hi.
>
> I've taken a look at http://www.sommarskog.se/. These articles are
> mainly geared towards v2000 and just about touches v2005. Anyways,
> what I was looking at was batch-abortion - ie execution stops,
> transactions are rolled back and error raised to the client.
>
> Can I force raise batch-abortion exceptions? This would solve my
> immediate requirements (I think)
>
> Riyaz
>

On Jun 18, 4:47pm, "Dan Guzman" <guzma...@nospam-
online.sbcglobal.net> wrote:
> Check out Erland's error handling articles athttp://www.sommarskog.se/.

Forgot to ask (re articles).

It talks about a SP nesting limit -> what value is this limit?
Does nesting within a transaction affect this limit?


Have also looked at AdventureWorks error handling. Its quite elegant
and simple -> unless done in a transaction (which seems to be the
default case). The logError sp *has* to be called after rolling back
all transactions (otherwise the logged error msg will be rolled back
as well). However, (thinking about it) the elegance (and practicality)
is lost if the error occurs 4 or 5 nested levels in, from where the
transaction was called.

I've thought about using a @ErrorMsg output parameter in all SP s (a
la global variable?). After any "execute SP" statements, an immediate
check on the length of @ErrorMsg and return if > 0 - continue
returning up the call stack. A try/catch block in client entry SP
catches other errors and again these can be passed to the client via
@ErrorMsg. Also, as I will be doing (atleast some) input data
validation, I could possibly return multiple validation errors whereas
raiserror or try/catch would return on the first error.

Am I rushing into a wall? Am quite new to tsql programming. I don't
want to be faced with a showstopper after a lot of implementation -
not if I can it.

Appreciate your thoughts/ideas on this method.

Riyaz

> It talks about a SP nesting limit -> what value is this limit?

32 levels deep.

> Does nesting within a transaction affect this limit?

No.

> I've thought about using a @ErrorMsg output parameter in all SP s (a
> la global variable?). After any "execute SP" statements, an immediate
> check on the length of @ErrorMsg and return if > 0 - continue
> returning up the call stack. A try/catch block in client entry SP
> catches other errors and again these can be passed to the client via
> @ErrorMsg. Also, as I will be doing (atleast some) input data
> validation, I could possibly return multiple validation errors whereas
> raiserror or try/catch would return on the first error.

I would use a non-zero stored procedure return code indicate failure. IMHO,
that is a Best Practice design pattern. You can still supply additional
information in validation errors in the @ErrorMsg OUTPUT variable.

--
Hope this s.

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

"Riyaz Mansoor" <riyaz.mansoor@gmail.com> wrote in message
news:3a88db58-376c-4a8e-b20c-e58b5cf3b71b@27g2000hsf.googlegroups.com...

On Jun 18, 4:47 pm, "Dan Guzman" <guzma...@nospam-
online.sbcglobal.net> wrote:
> Check out Erland's error handling articles athttp://www.sommarskog.se/.

Forgot to ask (re articles).

It talks about a SP nesting limit -> what value is this limit?
Does nesting within a transaction affect this limit?


Have also looked at AdventureWorks error handling. Its quite elegant
and simple -> unless done in a transaction (which seems to be the
default case). The logError sp *has* to be called after rolling back
all transactions (otherwise the logged error msg will be rolled back
as well). However, (thinking about it) the elegance (and practicality)
is lost if the error occurs 4 or 5 nested levels in, from where the
transaction was called.

I've thought about using a @ErrorMsg output parameter in all SP s (a
la global variable?). After any "execute SP" statements, an immediate
check on the length of @ErrorMsg and return if > 0 - continue
returning up the call stack. A try/catch block in client entry SP
catches other errors and again these can be passed to the client via
@ErrorMsg. Also, as I will be doing (atleast some) input data
validation, I could possibly return multiple validation errors whereas
raiserror or try/catch would return on the first error.

Am I rushing into a wall? Am quite new to tsql programming. I don't
want to be faced with a showstopper after a lot of implementation -
not if I can it.

Appreciate your thoughts/ideas on this method.

Riyaz

Thanks Dan.

On Jun 21, 6:17pm, "Dan Guzman" <guzma...@nospam-
online.sbcglobal.net> wrote:
> > It talks about a SP nesting limit -> what value is this limit?
>
> 32 levels deep.
>
> > Does nesting within a transaction affect this limit?
>
> No.
>
> > I've thought about using a @ErrorMsg output parameter in all SP s (a
> > la global variable?). After any "execute SP" statements, an immediate
> > check on the length of @ErrorMsg and return if > 0 - continue
> > returning up the call stack. A try/catch block in client entry SP
> > catches other errors and again these can be passed to the client via
> > @ErrorMsg. Also, as I will be doing (atleast some) input data
> > validation, I could possibly return multiple validation errors whereas
> > raiserror or try/catch would return on the first error.
>
> I would use a non-zero stored procedure return code indicate failure. IMHO,
> that is a Best Practice design pattern. You can still supply additional
> information in validation errors in the @ErrorMsg OUTPUT variable.
>
> --
> Hope this s.
>
> Dan Guzman
> SQL Server MVPhttp://weblogs.sqlteam.com/dang/