Does RAISERROR store values for @@ERROR?

I tried to write some test code that looks like:
DECLARE @.ErrorValue int
sp_addmessage 50001,10,'This is a test error message'
RAISERROR (50001,10,1)
SELECT @.ErrorValue = @.@.ERROR
If @.ErrorValue <> 0 ...etc.
I expected @.ErrorValue to be 50001, since I tried to move @.@.ERROR to a
variable immediately after the line that raised the error. Instead, a zero
value was returned. Why?
If I try to replace that line with:
RAISERROR (-100,10,1)
...which generates a system error 2758 ("RAISERROR could not locate entry
for error -100 in sysmessages"), and @.ErrorValue is set to 2758, so I know
the code is working.
Does RAISERROR not set the value of @.@.ERROR?
Thanks!> I expected @.ErrorValue to be 50001, since I tried to move @.@.ERROR to a
> variable immediately after the line that raised the error. Instead, a zero
> value was returned. Why?
The behavior is described completely in the documentation for raiserror.
You can use a different severity or override the behavior with an option.|||from BOL
"When an error is raised, the error number is placed in the @.@.ERROR
function, which stores the most recently generated error number. @.@.ERROR
is set to 0 by default for messages with a severity from 1 through 10."
Your second RAISERROR itself raised an error, which is what was returned
in @.@.ERROR, so that's not really a valid test.
Joel wrote:
> I tried to write some test code that looks like:
> DECLARE @.ErrorValue int
> sp_addmessage 50001,10,'This is a test error message'
> RAISERROR (50001,10,1)
> SELECT @.ErrorValue = @.@.ERROR
> If @.ErrorValue <> 0 ...etc.
> I expected @.ErrorValue to be 50001, since I tried to move @.@.ERROR to a
> variable immediately after the line that raised the error. Instead, a zero
> value was returned. Why?
> If I try to replace that line with:
> RAISERROR (-100,10,1)
> ...which generates a system error 2758 ("RAISERROR could not locate entry
> for error -100 in sysmessages"), and @.ErrorValue is set to 2758, so I know
> the code is working.
> Does RAISERROR not set the value of @.@.ERROR?
> Thanks!|||Ahh... that worked. I just tried the same code with an error level of 12, an
d
it works fine. Thanks for the help!
"Trey Walpole" wrote:

> from BOL
> "When an error is raised, the error number is placed in the @.@.ERROR
> function, which stores the most recently generated error number. @.@.ERROR
> is set to 0 by default for messages with a severity from 1 through 10."
> Your second RAISERROR itself raised an error, which is what was returned
> in @.@.ERROR, so that's not really a valid test.
> Joel wrote:
>sql

Does RAISERROR cause performance to go down slightly?

I was curious if using RAISERROR in the catch block of a stored procedure does actually causes some hit on performance? I think it would, as compared to simply returning an error code in this sp's output parameter.

It will cause some impact as RAISEERROR will inevitably require some extra resource, where as you rightly say an error code just requires a value to be set within an existing block of memory.