AND ( ','+@items+',' LIKE '%,'+tableName.field1+',%' )

quote:

---

( tableName.field1 = @item1 or tableName.field2 = @item2)

---

WHERE
    [Column1] IN (@var1, @var2, @var3)

WHERE
    ([column1] = @val1 OR @val1 IS NULL)
    AND/OR ([column2] = @val2 OR @val2 IS NULL)
    AND/OR ([column3] = @val3 OR @val3 IS NULL)
    ....
    ....

quote:

---

Originally posted by Transact Charlie

in that case, the string splitting function and then pass that to IN is probably the cleanest least impactful.

Read this for probably the 'best' way. (if you can make used of table valued parameters)
http://www.sommarskog.se/arrays-in-sql.html

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

---

SELECT ...
FROM dbo.TFPHistory
WHERE ...
      AND (dbo.TFPHistory.ItemName in (@ItemName))

SELECT ...
FROM dbo.TFPHistory
     JOIN dbo.MySplitFunction(@ItemName) AS S
         ON dbo.TFPHistory.ItemName = S.MySplitValue
WHERE ...

quote:

---

Originally posted by sunitabeck

SQL Server interprets your input parameter as a literal string - i.e., it is looking for any row where field1 is 'LFT,RGT'.

Alternatives are:
1. Make the entire select into a dynamic sql string. A very bad idea because of SQL injection risk.

2. Use a like clause as in:

AND ( ','+@items+',' LIKE '%,'+tableName.field1+',%' )

This has approach has two issues that you need to be aware of. First is that if there is an index on field1 this query won't be able to take advantage of that index. So performance may not be stellar. The second is that you have to make sure that your token separator (in this case comma) does not exist anywhere in the data in column field1.

3. Another widely used approach is to split the input string into a (virtual or temporary) table and join with that table. There are functions available online for doing the splitting - one I particularly like is in this blog: http://www.sqlservercentral.com/articles/Tally+Table/72993/ Copy and install the function in Figure 21 if you want to use it. While #2 is quick and easy, if you run into performance problems, this may be a better alternative.

4. Create an XML fragment that has all the items in your client code, and send that via the parameter in the stored proc. In the stored proc, shred the XML and join with the shredded XML.

I would start with #2 above and if that does not meet your needs, try 3 or 4.

---

AND ( ','+@items+',' LIKE '%,'+tableName.field1+',%' )

quote:

---

Originally posted by sunitabeck

You are welcome!

Be mindful of the performance issues and data requirements. If there is a large amount of data or if it is a mission-critical system, consider using the split function approach that Kristen showed in his example.

---

-- USe the tempdb so we don't screw anything up
USE tempdb
GO

-- Splitting function (not the fastest)
IF OBJECT_ID('udf_split') IS NOT NULL DROP FUNCTION udf_split
GO
 
CREATE FUNCTION udf_split(@s VARCHAR(8000), @sep VARCHAR(2))
RETURNS table
AS
RETURN (
    WITH Pieces([pn], [start], [stop]) AS (
      SELECT 1, 1, CHARINDEX(@sep, @s)
      UNION ALL
      SELECT [pn] + 1, [stop] + 1, CHARINDEX(@sep, @s, [stop] + 1)
      FROM Pieces
      WHERE [stop] > 0
    )
    SELECT [pn],
      SUBSTRING(@s, [start], CASE WHEN [stop] > 0 THEN [stop]-[start] ELSE 512 END) AS s
    FROM Pieces
  )
GO


DECLARE @myTable TABLE (
	[ID] INT IDENTITY(1,1) PRIMARY KEY
	, [Value] CHAR(1)
	)

INSERT @myTable ([Value])
VALUES ('a'),('b'),('c'),('d'),('e'),('f'),('g')

SELECT 'NO FILTER' AS [msg], * FROM @myTable

DECLARE @searchString VARCHAR(10) = 'a,b,g'

-- USING IN WITH A CALL TO THE TABLE VALUED FUNCTION
SELECT 'IN FILTER' AS [msg],  * FROM @myTable
WHERE
	[Value] IN ( SELECT [s] FROM tc.udf_split(@searchString, ',') )
	
-- JOINING TO THE TABLE VALUED FUNCTION
SELECT
	'JOIN FILTER' AS [msg]
	, m.[ID]
	, m.[Value]
FROM
	@myTable AS m
	JOIN tc.udf_split(@searchString, ',') AS sp ON sp.[s] = m.[Value]