Skip to Content

SQLScript Cursor not working

Hi,

Im using cursor as below code but it is not recognizing current row identifier. Could anybody please take a look.

CREATE PROCEDURE GET_SO_BO(IN KUNNR NVARCHAR(10))
	AS BEGIN
	DECLARE V_NETWR DECIMAL(15,2);
	BEGIN
	DECLARE CURSOR C1 FOR 
	SELECT VBELN NETWR FROM "CLF01"."VBAK" WHERE KUNNR = '0000002401';
		FOR cur_row AS C1
		DO
		CALL ins_msg_proc('DOCUMENT is: ' || cur_row.VBELN );
		END FOR;
		END;
		END;

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Feb 02, 2017 at 04:34 AM
    -1

    Hi Sudhanshu,

    Have you created the procedure ins_msg_proc.

    If not then follow these steps:

    1. CREATE TABLE message_box (p_msg VARCHAR(200), tstamp TIMESTAMP);

    2. CREATE PROCEDURE ins_msg_proc (p_msg VARCHAR(200)) LANGUAGE SQLSCRIPT AS BEGIN INSERT INTO message_box VALUES (:p_msg, CURRENT_TIMESTAMP); END;

    Then create your procedure:

    CREATEPROCEDURE GET_SO_BO(IN KUNNR NVARCHAR(10))ASBEGIN
    	DECLARE V_NETWR DECIMAL(15,2);BEGIN
    	DECLARE CURSOR C1 FORSELECT VBELN NETWR FROM"CLF01"."VBAK" WHERE KUNNR = '0000002401';FOR cur_row AS C1
    		DOCALL ins_msg_proc('DOCUMENT is: '|| cur_row.VBELN );ENDFOR;END;END;

    You can find the results in the table message_box

    SELECT * FROM message_box;

    Regards

    Raj

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 02, 2017 at 06:47 AM
    -1

    Hi Sudhanshu,

    I see two BEGINs in your code. Is that correct?

    I know it should work without but just for kicks, did you try to see what happens if you declare cur_row explicitly on the top?

    Regards,
    Akshat

    Add comment
    10|10000 characters needed characters exceeded