Need help with Stored Procedure | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Need help with Stored Procedure

Hi I have the Following Stored Procedure *****************
CREATE PROCEDURE NROSWI.AMEND_EVENT ( P_EVENT_ID INT, P_MODE INT, P_REASON VARCHAR(254), P_TERMINATION_DATE DATE, P_USER_ID INT)
SPECIFIC NROSWI.AMEND_EVENT
————————————————————————
— SQL Stored Procedure
————————————————————————
P1: BEGIN
DECLARE CONSTANT_AMEND_CODE_ID INT;
DECLARE CONSTANT_CANCEL_CODE_ID INT;
DECLARE NEW_EVENT_ID INT; SET NEW_EVENT_ID = NEXT VALUE FOR NROSWI.EVENT_SEQ;
SET CONSTANT_AMEND_CODE_ID = 54;
SET CONSTANT_CANCEL_CODE_ID = 56;
FOR L1 AS SELECT
NRCSWAP_APPLICATION_ID, NRCSWCT_EVENT_CODE_ID, NRCSWEV_NO_SEQUENCE,
NRCSWDP_DIST_PRACT_ID, NRCSWCT_LIFESPAN_CODE_ID,
NRCSWCT_INSTALLED_UNIT_CODE_ID,
NRCSWMX_MAXIMUM_ID, NRCSWAG_ALLOCATION_GROUP_ID,
NRCSWCT_RECONSTRUCTION_CODE_ID

FROM NROSWI.NRTSWI_EVENT
WHERE NRCSWEV_EVENT_ID = P_EVENT_ID DO INSERT INTO
NROSWI.NRTSWI_EVENT (NRCSWEV_EVENT_ID, NRCSWAP_APPLICATION_ID,
NRCSWCT_EVENT_CODE_ID, NRCSWEV_NO_SEQUENCE,
NRCSWDP_DIST_PRACT_ID, NRCSWCT_LIFESPAN_CODE_ID,
NRCSWMX_MAXIMUM_ID, NRCSWAG_ALLOCATION_GROUP_ID, …)
VALUES (NEW_EVENT_ID, L1.NRCSWAP_APPLICATION_ID,
CASE P_MODE WHEN 0 THEN CONSTANT_AMEND_CODE_ID ELSE
CONSTANT_CANCEL_CODE_ID END,
L1.NRCSWEV_NO_SEQUENCE + 1,
L1.NRCSWDP_DIST_PRACT_ID, L1.NRCSWCT_LIFESPAN_CODE_ID,
L1.NRCSWMX_MAXIMUM_ID, L1.NRCSWAG_ALLOCATION_GROUP_ID, ….);
END FOR;
***************** Problem is I need to get the maximum number from the NRCSWEV_NO_SEQUENCE and then +1 with it when i am inserting instead of L1.NRCSWEV_NO_SEQUENCE.. Can any one Help please
Not sure we can help you – you do not appear to be using syntax supported by Microsoft SQL Server 7.0 or 2000.
opps may be i am in a wrong forum.. this is DB2.. can the admins put this in the write one? or this is just for MS?
The forum is for MS SQL ‘only’, sorry about that. We do try to be as helpful as we can, and some of us do know a thing or two beyond MS SQL, so someone might still come up with an answer.
Well, this here is SQL Server land. [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />For questions related to DB2 check out either the comp.databases.ibm-db2 newsgroup (which is pretty good, btw) or<a target="_blank" href=http://www.dbforums.com>http://www.dbforums.com</a><br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)
thanks
]]>