How to parameterize IN clause in SQL Query to use in JMETER

Question

I need to parametrize a SQL Query to use in JMETER such that every time it triggers, a random value is picked from the list of values to be used in IN clause.

Parent Query - Select * from Employee where Emp_Id in ( 3,9,11,12,13) and Dept_Name in('HR',IT','Admin','Audit')

Post the Parameterization when i trigger the Query through JDBC Request the request run for different user needs to have random selection made. Ex: Query 1 should be like - Select * from Employee where Emp_Id in ( 3,9) and Dept_Name in('HR',IT')

Query 2 should be like - Select * from Employee where Emp_Id in ( 11,12,13) and Dept_Name in('HR',IT','Admin')

I am trying to use CSV Data Set Config but not able to achieve the above output.

Answer 1

First of all I would recommend reconsidering your whole approach because tests needs to be repeatable, if you need to check all the possible combinations of the emp and dept IDs - go for pairwise testing, store the generated queries in the CSV file and parameterize the queries using CSV Data Set Config.

If you still want to make the number of arguments absolutely random you can go for the following approach:

1. Add User Defined Variables to your Test Plan and define the following variables there:

   Emp_Id=3,9,11,12,13
   Dept_Name=HR,IT,Admin
   

2. Amend your query to include JMeter's __groovy() function like:

   Select * from Employee where Emp_Id in (${__groovy(def values = vars.get('Emp_Id').split('\,').collect(); values.shuffle(); values.take(org.apache.commons.lang3.RandomUtils.nextInt(1\,values.size())).join('\,'),)}) and Dept_Name in(${__groovy(def values = vars.get('Dept_Name').split('\,').collect{value -> "'" + value + "'"}; values.shuffle(); values.take(org.apache.commons.lang3.RandomUtils.nextInt(1\,values.size())).join('\,'),)})
   

Demo: