how can I pass single value or multiple values to the below same prompted condition?
P_ID IN #sq(prompt(''))#
thanks in advance.
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.
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,
token VARCHAR(20) CHARACTER SET UNICODE)
) AS d