One or more values to pass in a prompt

Database
Enthusiast

One or more values to pass in a prompt

I have a condition where I have single or miltiple values to filter. 

ex:  P_ID = 5056,1351  or 5001  or 20100

how can I pass single value or multiple values to the below same prompted condition?

P_ID IN  #sq(prompt(''))#  

thanks in advance.

3 REPLIES
Junior Contributor

Re: One or more values to pass in a prompt

How is the value for #sq(prompt(''))# filled/created?

If it's just plugged in the SQL string by the client, the simply use "(5056,1351)" or "(5001)".

If it's supposed to be a parameter for a Prepared SQL it's much mor complicated, i'd suggest using some Dynamic SQL in a SP.

But in both cases be aware of SQL Injection.

Dieter

Enthusiast

Re: One or more values to pass in a prompt

It supposed to be pass through parameter. Can I not do this with out creating a SP?

thanks,.

Junior Contributor

Re: One or more values to pass in a prompt

When you're  on TD14 you might simply use the strtok_split_to_table function:

WHERE P_ID IN
(
SELECT CAST(token as INT)
FROM TABLE (STRTOK_SPLIT_TO_TABLE(1, #sq(prompt(''))#, ',')
RETURNS (outkey INTEGER,
tokennum INTEGER,
token VARCHAR(20) CHARACTER SET UNICODE)
) AS d
)

Dieter