Help on Stored Procedures 1

[mynwo]

Dear All,

I need some help on procedures. I have a table which has around 5000 records. I need to insert additional 200 records, i have insert statements ready for the same. I need to make sure while inserting that if records does not exists in the table it should insert them or else update them, there is a unique constraint on the id. If records exists it throws me a unique constraint violation error. How can i do it with the help of procedure. Please advice.

Best Regards,

Lloyd

 

[vpshriyan]

Hi Lloyd,

How those 200 records are fed/input into the procedure? Is it available in an existing base table? If so you may consider the example outlined below.

This example may look familiar to your project, because I wrote it sometime back, as one of your colleague, wanted similar help.

CREATE PROCEDURE dataload_customer()
DEFINE cust_cd CHAR;
DEFINE stn_cd CHAR;
DEFINE cust_nm VARCHAR;
DEFINE cust_nm VARCHAR;
DEFINE cr_dt DATE;
DEFINE cr_tm CHAR(8);
DEFINE cr_by CHAR(8);
DEFINE cnt INT;
-- gather today's date LET cr_dt =select today from systables where tabid=1;
-- select all rows from source table one by one
FOREACH cur1 FOR SELECT customer_code,name
INTO cust_cd, cust_nm, cr_by, cr_dt, cr_tm
FROM ocslinktest:steps_customer
-- select user information
SELECT FIRST 1 user_id, user_station_cd
INTO cr_by, stn_cd FROM ocslinktest:user ;
-- search for existence of row in taget table
LET cnt = 0 ;
SELECT rowid INTO cnt FROM ocslinkdev:customer
WHERE customer_code=cust_cd and station_cd=stn_cd ;
IF cnt IS NULL THEN
LET cnt=0;
END IF
IF cnt = 0 then
-- row not found, insert one.
WHILE (1=1)
-- gather current time
LET cr_tm =select substr(to_char(current),12,19) from systables where tabid=1;
-- insert info into new table in different db
INSERT INTO ocslinkdev:customer VALUES( cust_cd, stn_cd, cust_nm, cr_dt, cr_tm, cr_by) ;
-- check to see the row is inserted with success
SELECT DBINFO('sqlca.sqlerrd2') INTO cnt FROM systables WHERE tabid=1;
IF cnt IS NULL THEN
LET cnt=0;
END IF
IF cnt=1 THEN
EXIT WHILE;
END IF
END WHILE
ELSE
-- row found, update it.
WHILE (1=1)
UPDATE ocslinkdev:customer
SET customer_nm = cust_nm, created_by = cr_by, creation_dt = cr_dt, creation_tm = cr_tm
WHERE customer_code=cust_cd and station_cd=stn_cd ;
-- check to see the row is updated with success
SELECT DBINFO('sqlca.sqlerrd2') INTO cnt FROM systables WHERE tabid=1;
IF cnt IS NULL THEN
LET cnt=0;
END IF
IF cnt=1 THEN
EXIT WHILE;
END IF
END WHILE
END IF
END FOREACH
END PROCEDURE ;

Regards,
Shriyan

 

[mynwo]

Hi Shriyan,

Thanks for your feedback.

Regards,

lloyd