Syntax error when creating stored procedure in DB2

Question

I am trying to create a stored procedure in DB2 which uses a cursor. However, I am having issues with the correct syntax and recieve the error:

    SQL0104N  An unexpected token "=" was found following "N FOR SET v_party_id".  
Expected tokens may include:  "JOIN".  LINE NUMBER=12.  SQLSTATE=42601

My procedure is as follows:

CREATE PROCEDURE Core.LWRH_LIST_CARRIER_EMAILS
(
    IN p_party_role_id BIGINT
)
LANGUAGE SQL
DYNAMIC RESULT SETS 1
BEGIN


DECLARE v_party_id BIGINT;
DECLARE c_result CURSOR WITH RETURN FOR
SET v_party_id = (Select party_id from core.party_role where party_role_id = p_party_role_id);

Select cm.contact_method_id, cm.contact_method_type_id, cm.electronic_address
from core.party_contact_method pcm
join core.contact_method cm
on cm.contact_method_id = pcm.contact_method_id
and cm.contact_method_type_id = 6
and pcm.party_id = v_party_id;



OPEN c_result;

END@

Can anybody advise on what the correct syntax would be?

Thanks

Ian Bjorhovde · Accepted Answer · 2015-03-11 18:15:44Z

2

You have your SET statement in the middle of your DECLARE CURSOR statement. It should look like:

...

DECLARE v_party_id BIGINT;

-- This doesn't execute the statement, just declares the cursor.
DECLARE c_result CURSOR WITH RETURN FOR
    select cm.contact_method_id, cm.contact_method_type_id, cm.electronic_address
    from   core.party_contact_method pcm
      join core.contact_method cm
        on cm.contact_method_id = pcm.contact_method_id
       and cm.contact_method_type_id = 6
       and pcm.party_id = v_party_id;


SET v_party_id = (Select party_id from core.party_role where party_role_id = p_party_role_id);


OPEN c_result;

...

answered Mar 11, 2015 at 18:15

Ian Bjorhovde

11.1k1 gold badge30 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

LiamWilson94 Over a year ago

Right okay, I thought that the statement was executed when the cursor was declared. That is why I did the set statement first as I thought the variables value would have not been set by the time it is used in the query. Thank you!

Gordon Linoff · Accepted Answer · 2015-03-11 11:56:21Z

0

At least one problem is that you do not define a cursor on a set statement. You define it on a select statement. So, this statement:

DECLARE c_result CURSOR WITH RETURN FOR
SET v_party_id = (Select party_id from core.party_role where party_role_id = p_party_role_id);

Doesn't make sense.

Try this:

DECLARE c_result CURSOR WITH RETURN FOR
    Select party_id
    from core.party_role
    where party_role_id = p_party_role_id;

Or, leave out the cursor, if there is only one row.

answered Mar 11, 2015 at 11:56

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

LiamWilson94 Over a year ago

I have previously tried moving the cursor declaration to where you have suggested and I then receive the error: An unexpected token "<cursor declaration>" was found following "". Expected tokens may include: "<SQL statement>

Collectives™ on Stack Overflow

Syntax error when creating stored procedure in DB2

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related