1

I have a DB2 stored procedures to get n number of sequence values and then combine them into a single comma delimited string and return it. The concat function in the stored procedure is not working as expected.

CREATE PROCEDURE REFWTX.GET_SEQ_VALUES (in numb integer, OUT SEQVALUES VARCHAR(10000))
LANGUAGE SQL
SPECIFIC GET_SEQ_VALUES
BEGIN
DECLARE SEQ_VAL Integer;
DECLARE CUR_COUNT INTEGER;
SET CUR_COUNT=1;
WHILE (CUR_COUNT <= numb) DO
SELECT NEXTVAL FOR REFWTX.ACK_999_INTR_CTRL_NO_SEQ INTO SEQ_VAL FROM SYSIBM.SYSDUMMY1;
set SEQVALUES = SEQVALUES|| ',' || CHAR(SEQ_VAL);
SET CUR_COUNT=CUR_COUNT+1;
END WHILE;
return;
END

The portion of the procedure: set SEQVALUES = SEQVALUES|| ',' || CHAR(SEQ_VAL);

is not working as expected. How do I concatenate strings in stored procedures?

Praveen
  • 31
  • 2
  • 3
  • I hope you're not planning on doing anything more than _display_ this information, as comma-separated lists are **extremely** difficult to work with in SQL (especially DB2). Actually, I believe LUW has some utilities for dealing with aggregating this kind of info, so you don't have to use a cursor (should be faster) - [this site has several examples](http://stackoverflow.com/questions/3728010/create-a-delimitted-string-from-a-query-in-db2/17452356#17452356). – Clockwork-Muse Jan 16 '14 at 08:04
  • Is SEQVALUES always set to the value that was set at first attempt? – JMD Apr 21 '16 at 23:47

1 Answers1

2

You haven't told us how it is "not working as expected". Example inputs and output would be useful.

My guess would be that since you never initialize SEQVALUES, it is set to NULL and concatenating anything with NULL gives you NULL.

Also, instead of 

SELECT NEXTVAL FOR REFWTX.ACK_999_INTR_CTRL_NO_SEQ INTO SEQ_VAL FROM SYSIBM.SYSDUMMY1;

why not use

 VALUES NEXTVAL FOR REFWTX.ACK_999_INTR_CTRL_NO_SEQ INTO SEQ_VAL;
Turophile
  • 3,367
  • 1
  • 13
  • 21