debugging stored procedure

Hi everyone,
For some reason our stored procedure is returning an error when
executed. As part of resolving where the point of failure occurs, I
have defined an OUT variable to return what stage within the stored
procedure execution has reached before failure:
CREATE PROCEDURE sp_generate_invoices @.id char(14), @.retval int = 0 OUT
AS
BEGIN
<code>
SET @.retvalue = 1
<more code>
SET @.retvalue = 2
...
END
RETURN 1
GO
--
My question is, if the stored procedure fails half way through (for
whatever reason for example an overflow error), is the @.retval value
prior to the failure actually returned when the procedure fails? Or is
the default value returned whenever failure occurs?
If not, I am guessing the best method to track where the point of
failure occurs is to include statements like:
insert flowcontrol (datestamp, position)
value (getdate(), '1')
... throughout the code.
Any insight into this most appreciated!
thanks,
peterSimplest way would be to use @.@.ERROR to trap for error conditions and using
PRINT statements to debug your flow.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
<mag1kus@.yahoo.com> wrote in message
news:1141009017.876445.302420@.p10g2000cwp.googlegroups.com...
> Hi everyone,
> For some reason our stored procedure is returning an error when
> executed. As part of resolving where the point of failure occurs, I
> have defined an OUT variable to return what stage within the stored
> procedure execution has reached before failure:
> --
> CREATE PROCEDURE sp_generate_invoices @.id char(14), @.retval int = 0 OUT
> AS
> BEGIN
> <code>
> SET @.retvalue = 1
> <more code>
> SET @.retvalue = 2
> ...
> END
> RETURN 1
> GO
> --
> My question is, if the stored procedure fails half way through (for
> whatever reason for example an overflow error), is the @.retval value
> prior to the failure actually returned when the procedure fails? Or is
> the default value returned whenever failure occurs?
> If not, I am guessing the best method to track where the point of
> failure occurs is to include statements like:
> insert flowcontrol (datestamp, position)
> value (getdate(), '1')
> ... throughout the code.
> Any insight into this most appreciated!
> thanks,
> peter
>