Assign multiple variables from select statement in Procedure

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

syntego@gmail.com - 29 Jun 2006 22:18 GMT

I am using DB2 V8 fixpack 10 and have the following issue:

>From a User Defined Function, I can assign multiple variables in a

single statement as follows:

-- Get the first update transaction for the mailing event.

set (fUpdateTS, fAddrType, fAccountID, fOpCode) =
(select AddTS, UpdateTypeCode, AccountID, OpCode
FROM
(select AddTS, UpdateTypeCode, AccountID, OpCode,
row_number() over(order by AddTS) as RNUM
from CANSAccountUpdates c
where c.MailingEventID = pMailingEventID
and c.UpdateTypeCode IN('LEGAL', 'JLEGL', 'ML',
'JML')
) as tmp
WHERE RNUM = 1
);

but when I try to use the same statement in a stored procedure, I get
the error when trying to compile:

SQL0104N An unexpected token "set" was found following " ". Expected
tokens may include <psm_while>. Line Number=78. SQLSTATE=42601.

Is there different way to assign multiple variable from SQL statement
in a DB2 stored procedure?

Many thanks,

Bob

Reply to this Message

Josh Tiefenbach - 30 Jun 2006 16:59 GMT

> SQL0104N An unexpected token "set" was found following " ". Expected
> tokens may include <psm_while>. Line Number=78. SQLSTATE=42601.
>
> Is there different way to assign multiple variable from SQL statement
> in a DB2 stored procedure?

Multi-target SET isnt currently supported in SQL stored procedures. Use
VALUES INTO or SELECT INTO instead.

Note that its not that bad to issue a series of single SET statements
in a SQL stored procedure - DB2 will often internally rewrite a series
of SET statements into a single VALUES INTO statement.

jsoh

Reply to this Message