Cathc error number from raiserror statement

[Subind]

Hi,
I have these two procs and I'm trying to catch the error number from
the proc "call_error".
But I always get 0 as the value. It may be bcoz of the "return 0" stmt
at the end of proc "throw_error" (as it executed successfully).
I cannot change the proc throw_error. So is there a way to capture the
error value from proc "call_error"?
Appreciate any help.

drop proc throw_error;
create proc throw_error
as
begin
raiserror 500000 "Oops here is the error!!!"
return 0
end

create proc call_error
as
begin
declare @rc int, @er int
exec @rc = throw_error
select @er = @@error

print "Return: %1!, Error: %2!", @rc, @er
end

exec call_error

Thanks.

[Leonid Gvirtz]

Hi Subind

Raiserror statement doesn't stop the batch execution, it just merely
displays user-defined error message and sets @@error variable. You can
verify it by adding "select @@error" after your raiserror statement.
From call_error point of view, throw_error finishes successfully, so
the behavior you have observed is expected. See this link for other
possible options: http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc32300.1502/html/sqlug/X51638.htm

Hope it helps
Leonid Gvirtz
http://www.gvirtz-consulting.com

[Subind]

> possible options:http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc32300...
>
> Hope it helps
> Leonid Gvirtzhttp://www.gvirtz-consulting.com

Thank Leonid for the prompt reply.
I realized it later after few discussions. But my requirement was such
that I had to catch the error no which is thrown by "throw error"
proc.
But later we had to scratch the idea.