CODEWITHSUNDEEP
sql
Mark
Mark

Reputation: 1

declaring a string to be passed into a sql query

I have a list of strings that i would like to pass multiple times in a SQL query.

I would like to declare them once and then pass the list into the queries. is this possible?

values:

'1-PGB4Z', '1-PGAT6', '1-PGB55', '1-PGB52',
'1-2SS1E6', '1-2SS1E3', '1-2RZQMZ', '1-2RZQNF', '1-2RZQMT', '1-2SACTZ', 
'1-2S0GNR', '1-2S0GLP', '1-2S0GCN', '1-1VOMUX', '1-1VOMV0', '1-1VOMV3', 
'1-1VOX7Z', '1-1VOX82', '1-1VOMV7', '1-1VOMVA', '1-1VOMVD', '1-1VOMVG', 
'1-1VPMBV', '1-1VPMBY', '1-1VPMC4', '1-1VO9XD', '1-1VO9XG', '1-1VO9XJ', 
'1-1VPMBY', '1-1VO9XJ', '1-1VO9XM', '1-1VO9Z6', '1-16PGRE', 
'1-16PGRK', '1-16PGRH', '1-8MNWB',  '1-9JAVH',  '1-8ISTX', '1-N5KYK', 
'1-KZMMH', '1-D7KG9', '1-9HCNP', '1-9H81K', '1-1C9F1P', '1-QBQQW',
'1-2S0F1P', '1-2S0F45', '1-2S17LK', '1-2S17LT', '1-2SL5D9', '1-2SDUQD'

part of query:

select * from table1 t3
where
 and t1.cse_id in 
(select d.cse_id 
from table1 d
where d.cse_employee_number in
(select substr(t2.emp_s3_id, 3,8) from table2 t2, table 3 T1 
where t2.fk_resource_group_id = t1.resource_group_id
and t1.resource_group_id in (
'1-PGB4Z', '1-PGAT6', '1-PGB55', '1-PGB52',
'1-2SS1E6', '1-2SS1E3', '1-2RZQMZ', '1-2RZQNF', '1-2RZQMT', '1-2SACTZ', 
'1-2S0GNR', '1-2S0GLP', '1-2S0GCN', '1-1VOMUX', '1-1VOMV0', '1-1VOMV3', 
'1-1VOX7Z', '1-1VOX82', '1-1VOMV7', '1-1VOMVA', '1-1VOMVD', '1-1VOMVG', 
'1-1VPMBV', '1-1VPMBY', '1-1VPMC4', '1-1VO9XD', '1-1VO9XG', '1-1VO9XJ', 
'1-1VPMBY', '1-1VO9XJ', '1-1VO9XM', '1-1VO9Z6', '1-16PGRE', 
'1-16PGRK', '1-16PGRH', '1-8MNWB',  '1-9JAVH',  '1-8ISTX', '1-N5KYK', 
'1-KZMMH', '1-D7KG9', '1-9HCNP', '1-9H81K', '1-1C9F1P', '1-QBQQW',
'1-2S0F1P', '1-2S0F45', '1-2S17LK', '1-2S17LT', '1-2SL5D9', '1-2SDUQD', 
'1-2SDUTD', '1-2FRRMN', '1-2GHUNX', '1-2FXDD6', '1-2FWFG1', '1-2FR8CR', 
'1-2FWFLM', '1-2FH7YL', '1-2FRRMX', '1-2GDIE3', '1-2FWFQI', '1-2FWFNL', 
'1-2U9ULR', '1-2U4ZZP', '1-2SC2WF', '1-2SXPKJ', '1-2TDSJ5')

Upvotes: 0

Views: 60

Answers (4)

Dani-Br
Dani-Br

Reputation: 2457

way 1:
ChrisBint is right. instead (val1,val2,val3) you can use (SELECT val FROM vals_tbl), and store the vals in vals_tbl.
It is the better way.

way 2: 

CREATE PROCEDURE exe_str(command TINYTEXT)/*exec the string*/
BEGIN
    PREPARE stmt1 FROM command;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;
END

Now, you can create string of the SELECT statement, and execute it : 

SET @str1="SELECT .........";
SET @str2="'1-PGB4Z', '1-PGAT6'........";
SET @str3=");";
CALL exe_str(CONCAT(@str1,@str2,str3));

I recommends the first way.

Upvotes: 0

Kinexus
Kinexus

Reputation: 12904

It would be better to store these values in a 'lookup' table and use this as the criteria within your query. If the values then changes, you do not have to go through the hassle of updating any stored procedures that use them.

For example

select * from table1 t3
where
 and t1.cse_id in 
(select d.cse_id 
from table1 d
where d.cse_employee_number in
(select substr(t2.emp_s3_id, 3,8) from table2 t2, table 3 T1 
where t2.fk_resource_group_id = t1.resource_group_id
and t1.resource_group_id in (SELECT value from lookup table)

As far as I am aware, there is no way to directly create a variable that is an array, but you can create a variable of VARCHAR that can be used with other functions to simulate searching an array. However this will not work directly within an IN statement

Upvotes: 1

Ēriks Daliba
Ēriks Daliba

Reputation: 718

It is NOT possible to define comma separated values and to use them as IN() statement (will not work). Temp table is the only solution.

Upvotes: 0

Saic Siquot
Saic Siquot

Reputation: 6513

if this are "permanent" values, fill a table with them and join it

Upvotes: 0

Related Questions