Stored Procedure Question

[zohab]

Hi,

 

I want to write stored procedure with one select query and 3 update queries.

 

I want to fetch 2 columns from first select query and use them in next 3 update queries.

 

Following is the procedure body.

 

PROCEDURE CHANGEORDER_TABLENAME(
P_ID NUMBER,
P_OLD_PARENT NUMBER,
P_DEST_PARENT NUMBER,
P_DEST_POSITION NUMBER
, RC1 OUT RCT) AS
BEGIN
OPEN RC1 FOR
SELECT TABLENAME_POSITION,TABLENAME_PARENT FROM AC_TABLENAME;
/*
SELECT TABLENAME_PARENT, TABLENAME_POSITION FROM AC_TABLENAME WHERE TABLENAME_ID = P_ID
UPDATE AC_TABLENAME SET TABLENAME_POSITION = TABLENAME_POSITION - 1 WHERE TABLENAME_POSITION > TABLENAME_POSITION AND TABLENAME_PARENT = TABLENAME_PARENT
UPDATE AC_TABLENAME SET TABLENAME_POSITION = TABLENAME_POSITION + 1 WHERE TABLENAME_POSITION => P_DESTPOSITION AND TABLENAME_PARENT = P_DESTPARENT
UPDATE AC_TABLENAME SET TABLENAME_POSITION = P_DESTPOSITION WHERE TABLENAME_PARENT => P_DESTPARENT AND ID = P_ID
*/
END;

 

- Thanks

Zohaib.

[kicken]

What SQL Engine are you using? The exact syntax will depend on the engine and possibly version. You might be able to just use a single UPDATE query and not need to select anything first.

[zohab]

Thanks Kicken for reply.

 

I got it working and I am using Oracle Database.

 

- Thanks

Zohaib.