Prev: Problem: MySQL time datatype to SQL Server 2005 Datetime
Next: generic datatype table
From: David Walker on 11 Jul 2008 17:21
Is this code valid in a proc?


<Some SQL statement>

If @@Error <> 0
Begin
RaisError('ProcName: Return code %i doing something', 16, 1, @@Error)
With SetError
Return @@Error
End


The Begin stataement shouldn't change the @@Error value, correct? The call
to RaisError shouldn't change the @@Error value before it's picked up as a
parm to RaisError, correct? And the "With SetError" clause should reset
@@Error to cause the Return statement to exit the proc with code 50000,
whcih is the default for RaisError.

When transactions are involved, there's more, but I wanted to make sure
this piece of code was doing what I am thinking it's doing. (Which is, to
print the original error code and exit the proc with error code 50000.)


Thanks.
From: KH on 11 Jul 2008 17:34
From BOL: "Because @@ERROR is cleared and reset on each statement executed,
check it immediately following the statement being verified, or save it to a
local variable that can be checked later."

It's not the BEGIN that's setting it to zero, it's "If @@Error <> 0" ...
which isn't likely to fail so your use in RAISERROR is probably always going
to be zero.

There's a couple comprehensive articles on error handling Erland
Sommarskog's site:
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html

HTH


"David Walker" wrote:

> Is this code valid in a proc?
>
>
> <Some SQL statement>
>
> If @@Error <> 0
> Begin
> RaisError('ProcName: Return code %i doing something', 16, 1, @@Error)
> With SetError
> Return @@Error
> End
>
>
> The Begin stataement shouldn't change the @@Error value, correct? The call
> to RaisError shouldn't change the @@Error value before it's picked up as a
> parm to RaisError, correct? And the "With SetError" clause should reset
> @@Error to cause the Return statement to exit the proc with code 50000,
> whcih is the default for RaisError.
>
> When transactions are involved, there's more, but I wanted to make sure
> this piece of code was doing what I am thinking it's doing. (Which is, to
> print the original error code and exit the proc with error code 50000.)
>
>
> Thanks.
>
From: Mike C# on 15 Jul 2008 10:06

"David Walker" <none(a)none.com> wrote in message
news:Xns9AD89C4B386B7DavidWalker(a)207.46.248.16...
> Is this code valid in a proc?
>
>
> <Some SQL statement>
>
> If @@Error <> 0
> Begin
> RaisError('ProcName: Return code %i doing something', 16, 1, @@Error)
> With SetError
> Return @@Error
> End
>
>
> The Begin stataement shouldn't change the @@Error value, correct? The
> call
> to RaisError shouldn't change the @@Error value before it's picked up as a
> parm to RaisError, correct? And the "With SetError" clause should reset
> @@Error to cause the Return statement to exit the proc with code 50000,
> whcih is the default for RaisError.

The If statement is resetting it. You need to assign it to a local
variable:

DECLARE @e int;
SET @e = @@error;
If @e <> 0
Begin
RaisError('ProcName: Return code %i doing something', 16, 1, @e)
With SetError
Return @e
End


From: Alex Kuznetsov on 15 Jul 2008 10:18
On Jul 11, 4:21 pm, David Walker <n...(a)none.com> wrote:
> Is this code valid in a proc?
>
> <Some SQL statement>
>
> If @@Error <> 0
> Begin
> RaisError('ProcName: Return code %i doing something', 16, 1, @@Error)
> With SetError
> Return @@Error
> End
>
> The Begin stataement shouldn't change the @@Error value, correct? The call
> to RaisError shouldn't change the @@Error value before it's picked up as a
> parm to RaisError, correct? And the "With SetError" clause should reset
> @@Error to cause the Return statement to exit the proc with code 50000,
> whcih is the default for RaisError.
>
> When transactions are involved, there's more, but I wanted to make sure
> this piece of code was doing what I am thinking it's doing. (Which is, to
> print the original error code and exit the proc with error code 50000.)
>
> Thanks.

If you are on 2005, consider using try/catch blocks instead of old
style error handling.
From: David Walker on 25 Jul 2008 16:20
"Mike C#" <xyz(a)xyz.com> wrote in
news:uhzZfPo5IHA.3784(a)TK2MSFTNGP06.phx.gbl:

>
> "David Walker" <none(a)none.com> wrote in message
> news:Xns9AD89C4B386B7DavidWalker(a)207.46.248.16...
>> Is this code valid in a proc?
>>
>>
>> <Some SQL statement>
>>
>> If @@Error <> 0
>> Begin
>> RaisError('ProcName: Return code %i doing something', 16, 1,
>> @@Error)
>> With SetError
>> Return @@Error
>> End
>>
>>
>> The Begin stataement shouldn't change the @@Error value, correct?
>> The call
>> to RaisError shouldn't change the @@Error value before it's picked up
>> as a parm to RaisError, correct? And the "With SetError" clause
>> should reset @@Error to cause the Return statement to exit the proc
>> with code 50000, whcih is the default for RaisError.
>
> The If statement is resetting it. You need to assign it to a local
> variable:
>
> DECLARE @e int;
> SET @e = @@error;
> If @e <> 0
> Begin
> RaisError('ProcName: Return code %i doing something', 16, 1, @e)
> With SetError
> Return @e
> End
>
>

Thanks; I hadn't thought of the IF statement resetting ERROR, since IF
is not really an "actionable" statement (or so I thought).

I appreaciate the help.

David Walker


 |  Next  |  Last
Pages: 1 2
Prev: Problem: MySQL time datatype to SQL Server 2005 Datetime
Next: generic datatype table