--- (pro_tax1 IN (@TIN)) 
','+@TIN+',' LIKE '%,'+	pro_tax1 + ',%'

quote:

---

Originally posted by James K

The quick way is to send the multiple values comma-separated in the @TIN parameter and change the WHERE clause to this:

--- (pro_tax1 IN (@TIN)) 
','+@TIN+',' LIKE '%,'+	pro_tax1 + ',%'

That may have some performance implications. So the recommended strategy would be to split the comma-separated string into individual tokens in a virtual (or temp) table and join to that table. There are string splitters available on line - for example here: http://www.sqlservercentral.com/articles/Tally+Table/72993/

---

CREATE TABLE #tmp(pro_tax1 VARCHAR(32));
INSERT INTO #tmp VALUES ('abcd'),('1234'),('xyz'),('0000'),('11111'),('77');

DECLARE @TIN VARCHAR(32) = 'abcd,xyz,0000,77,somethingThatDoesnotExist';
SELECT * FROM #tmp WHERE ','+@tin+',' LIKE '%,'+pro_tax1+',%'
-- returns 4 rows as expected

DROP TABLE #tmp;

quote:

---

Originally posted by James K

It should work for any number of parameters. Compare with the test code below to see what you might be doing differently. You can copy and paste it to an SSMS window to run it.

CREATE TABLE #tmp(pro_tax1 VARCHAR(32));
INSERT INTO #tmp VALUES ('abcd'),('1234'),('xyz'),('0000'),('11111'),('77');

DECLARE @TIN VARCHAR(32) = 'abcd,xyz,0000,77,somethingThatDoesnotExist';
SELECT * FROM #tmp WHERE ','+@tin+',' LIKE '%,'+pro_tax1+',%'
-- returns 4 rows as expected

DROP TABLE #tmp;

---

CREATE TABLE #tmp(pro_tax1 VARCHAR(32));
INSERT INTO #tmp VALUES ('abcd'),('1234'),('xyz'),('0000'),('11111'),('77');

DECLARE @TIN VARCHAR(32) = 'abcd, xyz,0000,77,somethingThatDoesnotExist';
SELECT * FROM #tmp WHERE ','+@tin+',' LIKE '%,'+pro_tax1+',%'
-- returns 4 rows as expected

DROP TABLE #tmp;

quote:

---

Originally posted by James K

You don't need to create a temp table. I was only demonstrating how it would/should work using the temp table because I don't have access to the actual tables you are using.

Examine the string that you are passing in. Subtle issues with that string can cause incorrect behavior. For example, in the example below, which is (almost) the same as what I posted earlier, you will get only 3 values in the result, when in fact you would be expecting four. This is because there is a space (that I deliberately added) before the "xyz" in the @TIN string. It could be issues like that that is causing your code to function incorrectly.

CREATE TABLE #tmp(pro_tax1 VARCHAR(32));
INSERT INTO #tmp VALUES ('abcd'),('1234'),('xyz'),('0000'),('11111'),('77');

DECLARE @TIN VARCHAR(32) = 'abcd, xyz,0000,77,somethingThatDoesnotExist';
SELECT * FROM #tmp WHERE ','+@tin+',' LIKE '%,'+pro_tax1+',%'
-- returns 4 rows as expected

DROP TABLE #tmp;

---

....
DECLARE @TIN VARCHAR(8000) = 
....