How to parametrize WHERE IN clause in stored procedure?

Database
Enthusiast

How to parametrize WHERE IN clause in stored procedure?

I'm trying to parametrize a stored procedure, which takes one input string parameter and use it in the where clause in dynamic cursor.

The following snippet works with one parameter but when the list is like "US,GE", then it fails..

CREATE PROCEDURE test(IN varList VARCHAR(50)) 
DYNAMIC RESULT SETS 1
BEGIN
DECLARE query VARCHAR(200);
DECLARE C1 CURSOR WITH RETURN TO CLIENT ONLY FOR S1;

SET query = 'SELECT * FROM SOME_TABLE WHERE country_cd IN (?);';

PREPARE S1 FROM query;
OPEN C1 USING varList;

END;

Any idea if this is posible and how to achieve this?

6 REPLIES
Junior Contributor

Re: How to parametrize WHERE IN clause in stored procedure?

A parameter is a single value, not a list of values.

You need a dynamic SQL string:

SET QUERY = 'SELECT * FROM SOME_TABLE WHERE country_cd IN ('|| varlist || ');';

PREPARE S1 FROM QUERY;
OPEN C1 ;

Dieter

Enthusiast

Re: How to parametrize WHERE IN clause in stored procedure?

Hi Dieter,

I understant that 'US,GE' <> 'US','GE' , the question is how to get the second from first?

I alreary tried what you suggest and it doesn't work.

SET QUERY = 'SELECT * FROM SOME_TABLE WHERE country_cd IN ('|| varlist || ');';

PREPARE S1 FROM QUERY;
OPEN C1 ;

The error thrown is

CALL test('US');

CALL Failed. 5628: test:Column US not found in Database.SOME_TABLE

Looks like the varlist variable in the result QUERY string is taken for a column name

Is there any important difference between the placeholder form and the concat form of the dynamic sql?

SELECT * ...WHERE code = ?

'SELECT * ... where code = ' || variable

Junior Contributor

Re: How to parametrize WHERE IN clause in stored procedure?

Ok, if you want to pass a list of strings, you have to escape all strings:

call test( '''US'',''GE''')

or you pass 'US,GE' and add the quotes within your SP using SQL or simply use oReplace:

'''' || OREPLACE('US,GR',',',''',''') ||''''

Depending on your definition of oReplace (resulting datatype) you may have to use:

'''' || CAST(OREPLACE('US,GR',',',''',''') AS VARCHAR(10000)) ||''''

Dieter

Enthusiast

Re: How to parametrize WHERE IN clause in stored procedure?

Hi Dieter,

I too am facing a similar situation where I need to pass a couple of Strings as parameter in WHERE clause in a SP.

I am not able to get the desired result with the SP I have.

REPLACE PROCEDURE mydb.mw_test_proc_k222 (
IN varList VARCHAR(200),
OUT valOut SMALLINT
)

BEGIN
DECLARE varListTmp VARCHAR(200);

SELECT '''' || OREPLACE(:varList, ',', ''',''') || '''' INTO varListTmp;

SELECT COUNT(BranchName) INTO :valOut FROM mydb.BranchK222
WHERE StateName IN ( :varListTmp );

END

CALL mydb.mw_test_proc_k222 ('QP,XY', valOut);

My Table Definition is:

CREATE MULTISET TABLE mydb.BranchK222 (
StateName CHAR(2),
BranchName VARCHAR(20)
) PRIMARY INDEX (StateName, BranchName);

Although, I get 13 from the below query, I get 0 through the SP.

SELECT COUNT(BranchName)
FROM mydb.BranchK222
WHERE StateName IN ('QP', 'XY');

I even tried calling the procedure like below without the OREPLACE bit in the SP:

CALL mydb.mw_test_proc_k222 ('''QP,XY''', valout);

No success till now.

Below is the sample data I was using:

INSERT INTO mydb.BranchK222 VALUES ('AB', 'Branch01');
INSERT INTO mydb.BranchK222 VALUES ('AB', 'Branch02');
INSERT INTO mydb.BranchK222 VALUES ('AB', 'Branch03');
INSERT INTO mydb.BranchK222 VALUES ('XY', 'AnoBranch01');
INSERT INTO mydb.BranchK222 VALUES ('XY', 'AnoBranch02');
INSERT INTO mydb.BranchK222 VALUES ('XY', 'AnoBranch03');
INSERT INTO mydb.BranchK222 VALUES ('XY', 'AnoBranch04');
INSERT INTO mydb.BranchK222 VALUES ('MN', 'KenoBranch01');
INSERT INTO mydb.BranchK222 VALUES ('MN', 'KenoBranch02');
INSERT INTO mydb.BranchK222 VALUES ('KO', 'NanoBranch01');
INSERT INTO mydb.BranchK222 VALUES ('KO', 'NanoBranch02');
INSERT INTO mydb.BranchK222 VALUES ('KO', 'NanoBranch03');
INSERT INTO mydb.BranchK222 VALUES ('QP', 'HaloBranch01');
INSERT INTO mydb.BranchK222 VALUES ('QP', 'HaloBranch02');
INSERT INTO mydb.BranchK222 VALUES ('QP', 'HaloBranch03');
INSERT INTO mydb.BranchK222 VALUES ('QP', 'HaloBranch04');
INSERT INTO mydb.BranchK222 VALUES ('QP', 'HaloBranch05');
INSERT INTO mydb.BranchK222 VALUES ('QP', 'HaloBranch06');
INSERT INTO mydb.BranchK222 VALUES ('QP', 'HaloBranch07');
INSERT INTO mydb.BranchK222 VALUES ('QP', 'HaloBranch08');
INSERT INTO mydb.BranchK222 VALUES ('QP', 'HaloBranch09');

- Thanks

Shardul

Junior Contributor

Re: How to parametrize WHERE IN clause in stored procedure?

You're not using Dynamic SQL, :varListTmp is always treated as a single value.

Try STRTOK_SPLIT_TO_TABLE:


Enthusiast

Re: How to parametrize WHERE IN clause in stored procedure?

Thank you Dieter!

It worked.

Copying my working code here for reference:

REPLACE PROCEDURE mydb.mw_test_proc_k222 (
IN varList VARCHAR(200),
OUT valOut SMALLINT
)

BEGIN

SELECT COUNT(BranchName) INTO :valOut FROM mydb.BranchK222
WHERE StateName IN (
SELECT D.Token
FROM TABLE (STRTOK_SPLIT_TO_TABLE(1, :varList, ''',''')
RETURNS ( OutKey INTEGER,
TokenNum INTEGER,
Token VARCHAR(200) CHARACTER SET UNICODE )
) AS D
);

END

CALL mydb.mw_test_proc_k222 ('QP,XY', valout);