Prev: Not nullable field doesn't get set to default on virtual server
Next: sqlcrl exception
From: new DBA in '09 on 29 Mar 2010 14:35
Hi Everybody,

A developer submitted T-SQL for a new stored procedure and I'm
confused about the last line:

CREATE PROCEDURE @SprocThatEndsWithRaiserror (@p1 int) AS
BEGIN
--numerous statements
RAISERROR (@ErrorMsg, 0, 1) WITH NOWAIT
END

It's not part of any conditional statement, it's simply going to run
at the end of the stored procedure. This procedure only gets called
from another procedure, never directly from the app. The calling
procedure executes this stored procedure and then checks the @ERROR
value in this manner:

EXEC @RetVal = MyDB.dbo.SprocThatEndsWithRaiserror @p1 = @value1
SET @ErrNo = @ERROR
IF @ErrNo <> 0 OR @RetVal <> 0 BEGIN
--do some stuff, and then...
RAISERROR (@Msg, 16, 1)

I can see that the sproc1 RAISERROR passes an error number value of 0,
so the calling procedure will be looking for any value other than
zero. Assuming that sproc1 errors out before encountering the
RAISERROR statement, the @ERROR value in the calling procedure will
not be zero. But in that case, isn't the RAISERROR statement in sproc
1 useless? Seems to me it's raising an error statement even when
there is no error.

The developer only included this because it's in another production
procedure he used as a template.

What reason would there be to include the first sproc's RAISERROR
statement?

Thanks,
Eric
From: Tom on 29 Mar 2010 18:24
On Mar 29, 2:35 pm, "new DBA in '09" <ericbra...(a)gmail.com> wrote:
> Hi Everybody,
>
> A developer submitted T-SQL for a new stored procedure and I'm
> confused about the last line:
>
> CREATE PROCEDURE @SprocThatEndsWithRaiserror (@p1 int) AS
> BEGIN
>   --numerous statements
>   RAISERROR (@ErrorMsg, 0, 1) WITH NOWAIT
> END
>
> It's not part of any conditional statement, it's simply going to run
> at the end of the stored procedure.  This procedure only gets called
> from another procedure, never directly from the app.  The calling
> procedure executes this stored procedure and then checks the @ERROR
> value in this manner:
>
> EXEC @RetVal = MyDB.dbo.SprocThatEndsWithRaiserror @p1 = @value1
> SET @ErrNo = @ERROR
> IF @ErrNo <> 0 OR @RetVal <> 0 BEGIN
> --do some stuff, and then...
> RAISERROR (@Msg, 16, 1)
>
> I can see that the sproc1 RAISERROR passes an error number value of 0,
> so the calling procedure will be looking for any value other than
> zero.  Assuming that sproc1 errors out before encountering the
> RAISERROR statement, the @ERROR value in the calling procedure will
> not be zero.  But in that case, isn't the RAISERROR statement in sproc
> 1 useless?  Seems to me it's raising an error statement even when
> there is no error.
>
> The developer only included this because it's in another production
> procedure he used as a template.
>
> What reason would there be to include the first sproc's RAISERROR
> statement?
>
> Thanks,
> Eric

Stored procedures always have an INT return code usually provided with
a return statement. Perhaps someone in the long chain of copy code did
not know how to get the return code but did know how to get the errror
code in the application. There is no reason to do this and infact it
if used everywhere will be a performance hit similar to the rowcount
returned when SET NOCOUNT is not used. The error message is another
bit of network traffic sent back to the application.
From: new DBA in '09 on 29 Mar 2010 19:17
Thank you, Plamen and Tom, for helping me yet again. I'm inclined to
believe this line was first included a long time ago to a single
procedure, probably for testing, but never got removed. Instead of
being analyzed critically, it's probably just copied and modified
slightly, thereby persisting and multiplying...kind of like a virus.

"Because we've always done it that way" is certainly nothing new.

Again, thank you both.

-Eric
 | 
Pages: 1
Prev: Not nullable field doesn't get set to default on virtual server
Next: sqlcrl exception