Parameters in stored procedures

[Bart Pietercil]

On 20-sep-07, at 15:00, Ivan Smahin wrote:

> Hello Bart,
>
> Thursday, September 20, 2007, 3:33:07 PM, you wrote:
>
>>> CREATE PROCEDURE sp1()
>>> BEGIN
>>>     RAISE 50001, 'Some user error';
>>> EXCEPTION
>>> WHEN 50001 THEN
>>>     SET @v1 = 10;
>>> END
>
>> Is this exception also known in the calling app.
>> In other words can I in the exception handler of Realbasic display a
>> dialog that says 'Some user error'  when I 'raise 50001, 'Some user
>> error' inside the stored procedure.
>> The idea is when I get an invalid login I raise a user error 'Invalid
>> Login' that needs to be displayed inside the frontend (calling)
>> application.
>
> In  given  example  exception  is  caught  inside SP. So app will know
> nothing.
>
> To make it aware of some exception 50001:
> 1.  Don't  catch 50001 exception in SP and it will be throw outside as
> usual.
>
> 2.  Set  some  variable  ( SET @errCode = 50001 ) in SP error handler.
> Later - you can "SELECT @errCode" and do some logic.
>
> 3. More sophisticated way:
>
> CREATE PROCEDURE sp1()
> BEGIN
>     INSERT INTO t1 VALUES(1);
>     INSERT INTO t1 VALUES(1);
> EXCEPTION
> WHEN ERR_CONSTRAINT_UNIQUE_VIOLATION THEN
>    RAISE 50001, 'Login failed because of unique violation in sp1';
> END
>
> // Some pseudo-code:
> try
> {
>  db.SQLExecute( 'sp1' );
> }
> catch( xException& ex )
> {
>   display(  ex.ErrorCode, ex.errMessage ); // Shows up: 50001,  
> Login failed because of unique violation in sp1'
> }
>
>
>> Do I understand correctly that statements following the raise are NOT
>> executed. So raise can be considered the same as EXIT ?
>
> Yes, correct.
>
OK, continuing the path....

create or replace procedure spLogin(in vc_logon varchar,IN  
vc_password varchar,OUT userid long)
begin
	
	set userid = select recid from tbl_users where user_logonname =  
vc_logon AND user_password = vc_password;
	IF userid IS NOT null then
		SET @user_id = userid;
		update tbl_users SET last_login_dt = now() WHERE recid = @user_id;
	else
		raiseerror 50001,'Invalid login';
	END if

end

generates this error : 15:03:53: Error: Kernel error: 0x71000. line  
9:14: unexpected token: 50001

when I use raise NO problem; looking at the wiki it looked as if  
raise and raiseerror where the same ?

thanks

Bart Pietercil