Dynamic IN values

Transact-SQL
1

Hi, I am writing an sql query where I want to pass no. of values to IN but I don't know how many values may come during run time.
for eg.

  1. SELECT * FROM WHERE EMP_NAME IN ('ABC', 'XYZ', 'PQR')
  2. SELECT * FROM WHERE EMP_NAME IN ('ABC', 'XYZ', 'PQR', 'VWX', 'STY', DEF')
  3. SELECT * FROM WHERE EMP_NAME IN ('ABC', 'XYZ', 'PQR', 'IWX', 'NAP', 'FPE', 'DEQ', 'RRLY' )
    so in first query IN is having only 3 values, in 2nd 5 values and in 3rd we have 8 values, values in IN are not fixed, it may vary, how would I handle it?
    Please help.
2

I would suggest using a table instead of an IN. That was the table can have as many values as you need.

CREATE TABLE #mytab (EmpName VARCHAR(10));
INSERT INTO #mytab VALUES 
('ABC'),('XYZ'),('PQR'); -- This can be however many you need
SELECT BaseTable.* 
FROM BaseTable 
INNER JOIN #mytab ON BaseTable.Emp_Name = #mytab.EmpName;

Also replace the * with the list of columns needed.

1 Like
3

Got your idea..thanks djj55 :slight_smile: