Stored Procedures - Need help with massaging data within an input parameter

Database
Enthusiast

Stored Procedures - Need help with massaging data within an input parameter

Hello everyone,

I've got a question regarding what I can do with the data inside of a parameter passed to a stored procedure.  The procedure would start out like this:

REPLACE PROCEDURE DBASEWORK.PRC_MYPROC(

pPARAMETER1 VARCHAR(10000)

)

CREATE MULTISET VOLATILE TABLE TEMPVAL, NO LOG

(PARAMETERNAME VARCHAR(50))

UNIQUE PRIMARY INDEX (PARAMETERNAME)

ON COMMIT PRESERVE ROWS;

Let's say that I want to pass procedure a parameter that looks like this:

CALL DBASEWORK.PRC_MYPROC('VALUE1, VALUE2, VALUE3');

Is there a way for me to take this value and split it up into 3 separate rows in my TEMPVAL table?  So essentially it would be split up into 3 rows in this table;

ROW1: VALUE1

ROW2: VALUE2

ROW3: VALUE3

This wouldn't be 3 values every time, but rather it could be a dynamic amount.  Could have ('VALUE1, VALUE2') or ('VALUE1, VALUE2, VALUE3, VALUE4..., VALUEX).  Realistically, the most values that would be within this input parameter would be around 10, so it wouldn't be doing any serious processing. 

Any ideas would be extremely helpful, please reply if I can help be more clear. 

4 REPLIES
Enthusiast

Re: Stored Procedures - Need help with massaging data within an input parameter

As PL/SQL is allowed in Stored Procedure, so you can take the input parameter and by doing the string operations to split the results and insert them in the table.

You will need to use the INDEX/POSITION function to find the position of comma and then do a SUBSTRING operation to get the required results...

Enthusiast

Re: Stored Procedures - Need help with massaging data within an input parameter

I guess I don't know what code to use to loop through the parameter.  I would use SUBSTRING and POSITION to figure out where the first value ends and the 2nd starts, and insert that value into the TEMP table. 

But then I'm not sure how I would loop back and do the process again, with the parameter now starting with the 2nd value that was initially passed. 

If the parameter pased was ('VALUE1, VALUE2, VALUE3) I guess logically it would flow like this:

1) do the first SUBSTRING statement and insert the row into the temp table:

2) Set a declared environment variable to equal the entire parameter, minus the first value that was just substring'd off

3) repeat process using the new environment variable

4) stop once the environment variable is empty. 

If anyone could give an example of what this loop would like, it would help me out a ton. 

Senior Apprentice

Re: Stored Procedures - Need help with massaging data within an input parameter

This is an example from my trainings, it's no loop, but recursion, yet it's the same logic:

/*** How to split a comma delimited list of values ***/

DROP TABLE Strings;

CREATE TABLE Strings
(
groupcol INT NOT NULL,
string VARCHAR(991) NOT NULL
);

/*** Some comma delimited strings ***/
INSERT INTO Strings VALUES (1,'a,b,c,d,e');
INSERT INTO Strings VALUES (2,'a,bb,ccc,dddd,eeeee');

/*** Always be prepared for exceptional values... ***/
INSERT INTO Strings VALUES (3,'a,,bb');
INSERT INTO Strings VALUES (4,',a');
INSERT INTO Strings VALUES (5,'a,');
INSERT INTO Strings VALUES (6,',');
INSERT INTO Strings VALUES (7,'');

collect statistics on strings column groupcol;

/***
Recursive SQL
CAUTION, this query might need a lot of spool, because the string (or the rest of it)
is used for each new level,
e.g. 1000 chars, 100 values/result rows --> ~(1000*100/2) = 50000 chars in spool, i.e. 50x the size of the original column
***/
WITH RECURSIVE cte
(groupcol,
--string,
len,
remaining,
word,
pos
) AS (
SELECT
GroupCol,
-- String,
POSITION(',' IN String || ',') - 1 AS len,
SUBSTRING(String || ',' FROM len + 2) AS remaining,
SUBSTRING(String FROM 1 FOR len) AS word,
1
FROM strings
UNION ALL
SELECT
GroupCol,
-- String,
POSITION(',' IN remaining)- 1 AS len_new,
SUBSTRING(remaining FROM len_new + 2),
SUBSTRING(remaining FROM 1 FOR len_new),
pos + 1
FROM cte
WHERE remaining <> ''
)
SELECT
groupcol,
pos,
word
FROM cte
--WHERE word <> '' /** to exclude empty strings **/
ORDER BY
groupcol, pos;

/***
Recursive SQL
This version has to join back to the strings table for each level, but spool usage is less.
***/
WITH RECURSIVE cte
(groupcol, start_pos, len) AS
(
SELECT
GroupCol,
1 (INT) AS start_pos,
POSITION(',' IN String || ',') - 1 AS len
FROM strings
UNION ALL
SELECT
cte.GroupCol,
cte.start_pos + len + 1 AS next_start,
POSITION(',' IN SUBSTRING(s.string FROM next_start) || ',')- 1 AS len_new
FROM cte JOIN strings s
ON cte.groupcol = s.groupcol
WHERE next_start <= CHAR_LENGTH(s.string)
)
SELECT
cte.*,
SUBSTRING(s.string FROM start_pos FOR len) AS word
FROM cte JOIN strings s
ON cte.groupcol = s.groupcol
--WHERE word <> '' /** to exclude empty strings **/
ORDER BY cte.GroupCol, start_pos
;

You can Insert/Select the result into TEMPVAL.

Caution: Support of RECURSIVE was added (afair) in TD13, before you have to stick to a WHILE loop.

Btw, the easiest solution would involve a table UDF to split the data into rows (there's one builtin in TD14, i can't remember it's name).

Dieter

Enthusiast

Re: Stored Procedures - Need help with massaging data within an input parameter

Dieter - You are a gentlemen and a scholar!  That was exactly what I needed and it worked beautifully.  Thank you very much for your kindness.