how to raise error in stored procedure

[Alex]

Hi,
How to raise error in stor proc like

CREATE PROCEDURE ALEX.del_from_table2()
LANGUAGE SQL
P1: BEGIN
DECLARE X integer default 0;
select count (*) into X from table 1;

if (X > 0)
SIGNAL '12345' set SET MESSAGE_TEXT ='can't delete from table 2 ,table1
still has values ';
else
delete from table2;
END P1

thank you

Alex

[Maxime Tiran]

SIGNAL is the right statement to use to raise an error in a stored
procedure. I think the problem in your code is the syntax:

malformed IF ... THEN ... ELSE ... END IF;
malformed string literal for your error msg (protect your single quote
with '')
malformed SIGNAL SQLSTATE 'xxxxxx' SET MESSAGE_TEXT='xxxxx';

You should have something like:

CREATE PROCEDURE ALEX.del_from_table2()
LANGUAGE SQL
P1: BEGIN
DECLARE X integer default 0;
select count (*) into X from table1;

if (X > 0) THEN
SIGNAL SQLSTATE '12345' SET MESSAGE_TEXT ='can''t delete from table 2

,table1 still has values ';
else
delete from table2;

end if;
END P1 !

Maxime Tiran
Software Engineer
IBM DB2 Migration Toolkit aka. MTk
www.ibm.com/db2/migration/mtk

[Brien Schultz]

In a Java SP?

"Alex" <t2...@hotmail.com> wrote in message
news:c8f952c7.03051...@posting.google.com...