Adding Exception handling condition in CASE Expression

[former_member597564]

select count(*) into lv_count from "****.synonyms::Src_STM" where "ID" = :user_input;

CASE WHEN lv_count > 0 THEN :user_input else "Throw an Exception" END

[ErvinSzolke]

Hi Nagendra,

can you plesae elaborate what your question is? This way you will have a higher chance to get an answer.

Thanks and Regards,

Ervin (moderator)

[former_member597564]

I need to thrown an exception when the user input entry is not present in another table which I am referring to synonym here.

How the procedure will throw an exception ?

I have declared exit handler

[pfefferf · Accepted solution]

As well as for several other questions of you the official documentation already contains the answer to your question. Please check the documentation for the SIGNAL. Maybe at some time the point is reached when it is really worth to have a look the documentation :).

[former_member597564 · Accepted solution]

DECLARE invalid_input CONDITION FOR SQL_ERROR_CODE 10000;

CASE WHEN lv_count > 0 THEN :ssid[:i] else SIGNAL invalid_input SET MESSAGE_TEXT ='Invalid input arguments' END

error :

Syntax error: "incorrect syntax near "invalid_input"" [8250009]

[pfefferf · Accepted solution]

Please can you share your whole procedure code.

[former_member597564 · Accepted solution]

Hi Florian,

Here is the code

PROCEDURE "a.b.c"(IN INPUT "abcd",

OUT OUTPUT "output_message",OUT EX_error_MESSAGE nvarchar(255) )

LANGUAGE SQLSCRIPT

SQL SECURITY INVOKER

AS

BEGIN

for i in 1..cardinality(:Id) do

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN

ROLLBACK;

Error_message = 'SQL Exception occured.Error Code is:'||::SQL_ERROR_CODE||'Error message is:'||::SQL_ERROR_MESSAGE;

END;

select count(*) into lv_count from table3 where…..

if abcd =” ‘’ and lv_count > 0

then

insert into table1….."

insert into table2…..

success_message = '' ||' ' ||:newid||' '|| 'created successfully';

INSERT INTO "adt_toolshed.adt_db.tables::id" VALUES (:success_message);

elseif abcd = “ '' AND lv_count = 0

RESIGNAL SET MESSAGE_TEXT = 'for the input parameter in_var = '||

:xyz[i] || ' exception was raised ';

Else

select max("VERSION") into vers FROM "table2" where "xxxxx" = :yyy

insert into table2

update table1

end if;

OUTPUT = select * from "dummy";

end for;

TRUNCATE TABLE "dummy";

END;

error :

incorrect syntax near resignal

[pfefferf · Accepted solution]

Man, you should really be consistent. In the first comment you wrote about an error for a condition defintion, now for resignal. That makes no fun.

Regaring your 2nd version I would say that a "then" is missing after the elseif condition.