How to use variables as an expression inside stored proc

Help
1

Hi Team,

I have a string/text variable suppose var_valid_country ="‘USA’,‘UK’" I am trying to use this variable in a if condition inside stored proc like :

CREATE OR REPLACE PROCEDURE P1 ( )
AS
Declare
var_valid_country varchar(255) ="‘USA’,‘UK’,‘CANADA’,‘INDIA’"
flag varchar (10) = Null;
BEGIN

IF country IN (var_valid_country)
Then
flag = ‘Y’
else
flag = ‘N’
end if;

END ;

Although its a valid country but still its giving flag value as N.

Can anyone please assist me on this kind of scenario.

2

Your code is treating the whole string

“‘USA’,‘UK’,‘CANADA’,‘INDIA’”

as a single string in the IN list. Try putting all the individual strings in an array and writing a UDF “contains_string” to loop through it and return true/false if the desired string is there

Or, if you really want that condition to be in SQL, not in procedural statements in an SP, you could use dynamic SQL (executed immediate).

1 Like
3

Thanks Hanson for your valuable suggestion.

1 Like
4

You can also do this kind of thing and combine it with whatever other SQL operations you need to join or filter:

singlestore> select * from table(json_to_array(concat('[', '"US","UK"', ']')));
+-----------+
| table_col |
+-----------+
| "US"      |
| "UK"      |
+-----------+
1 Like
5

If the list of valid countries has several values, as you show, I would put them in a table. Then you can do something like this:

SELECT CASE WHEN EXISTS(SELECT *
FROM tblValidCountries WHERE Country = var_valid_country) THEN 'Y'
ELSE 'N'
END;

This way, if the list of valid countries ever changes, you can update a table, versus a code change. And depending on how you use the procedure upstream, you might be able to use that EXISTS function in line and avoid having a separate procedure.
If you do use a separate procedure or function, consider returning a boolean value, since that’s what you are logically coming up with anyway.

1 Like