Jump to content

Archived

This topic is now archived and is closed to further replies.

zohab

Stored Procedure Question

Recommended Posts

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Thanks Kicken for reply.

 

I got it working and I am using Oracle Database.:)

 

- Thanks

Zohaib.

Share this post


Link to post
Share on other sites

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.