Parameters in stored procedures

[Ivan Smahin]

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.


-- 
Best regards,
Ivan Smahin 
Senior Software Engineer
Paradigma Software, Inc
Valentina - The Ultra-Fast Database
http://www.valentina-db.com