LastErrorNumber & LastErrorMessage

[Bart Pietercil]

Hi List,

this stored procedure seems to work

[code]
create or replace procedure cm_spLogin(in vc_logon varchar,IN  
vc_password varchar)
begin
DECLARE users_in_table integer;
declare userid integer;
DECLARE logon VARCHAR;
DECLARE intCount INTEGER;
set logon = CONCAT(vc_logon,vc_password);
SET intCount = SELECT count(*) FROM tbl_users where unique_logon =  
logon;
SET userid = 0;
IF intCount <> 0 THEN
set userid = select recid from tbl_users where unique_logon = logon;
SET @user_id = userid;
update tbl_users SET last_login_dt = now() WHERE recid = @user_id;
else
SET users_in_table = 0;
set users_in_table = SELECT count(*) FROM TBL_USERS;
IF (users_in_table = 0) then
raise 50002,'no users in table';
ELSEIF (users_in_table > 0) then
raise 50001, 'invalid login';
end IF
END if
exception
when others THEN
call cm_CoreErrorHandler();
end;
[/code]

because when it call it like this

CALL cm_spLogin('badname','badpassword')

the procedure cm_CoreErrorHandler is called

This sp looks like this:
[code]
create or replace procedure cm_CoreErrorHandler()
begin
declare @errorStr varchar(2044);
@errorStr = concat('time: ',now(),' user with id ', at user_id,'  
generated error with id ',@@LastErrorNumber,': ',@@LastErrorMessage);
print @errorStr;
end;
[/code]

In the warning log appears:
Print : intCount=0
Print : time: 2007-10-10 22:24:49:889 user with id 1 generated error  
with id 327681: invalid login

Now to the point

1) (minor point) Why in the print line it says id 327681 when I raise  
50001----> Hex ?
2 when I call the cm_spLogin like this:

call cm_spLogin('test','b210364');
select @@LastErrorNumber AS 'ErrorID', @@LastErrorMessage as  
'ErrorMessage';age;


it get the correct print line in warning_log but the cursor looks like:
ErrorID = 0 and ErrorMessage = <Null>

Are the @@Variables immediately reset when being read(printed) once ?

Please advice

TIA

Bart Pietercil