How do we write below pseudo code in db2,
If (Proc exists)
Drop Proc
Create Proc
Else
Create Proc
One solution I found, after googling is to ignore the return codes. Do we have a more elegant way to do this?
Thanks
Update: With the help of the answer below we wrote a proc as below to drop the procedures
CREATE PROCEDURE SVCASNDB.DROPSP(IN P_SPECIFICNAME VARCHAR(128))
SPECIFIC DROPSP
P1: BEGIN
-- Drop the SP if it already exists
if exists (SELECT SPECIFICNAME FROM SYSIBM.SYSROUTINES WHERE SPECIFICNAME = trim(upper(p_SpecificName))) then
begin
DECLARE v_StmtString VARCHAR (1024);
SET v_StmtString = 'DROP SPECIFIC PROCEDURE SCHEMA.' || p_SpecificName;
PREPARE stmt1 FROM v_StmtString ;
EXECUTE stmt1;
end;
end if;
END P1
From stackoverflow
-
this query: SELECT DISTINCT ROUTINENAME, RESULT_SETS, REMARKS FROM SYSIBM.SYSROUTINES where ROUTINESCHEMA='' AND FUNCTION_TYPE NOT IN ('S', 'T') (where you specify your schema name at the placeholder) gives you all procs in a schema. So the Proc exists part is simply an EXISTS query on that view with the proper proc name.
0 comments:
Post a Comment