How to Efficiently Pass Comma-Delimited Values to a SQL Server IN Function?

Pass delimited values ​​to SQL Server IN function

Passing comma separated string values ​​to the IN function in SQL Server can be a challenge. Direct use of the IN function containing delimited strings will result in conversion errors.

Option 1: Dynamic SQL

One way is to use dynamic SQL, which allows you to build IN clauses dynamically. However, this method is not recommended due to security and performance concerns.

Option 2: String operation

A more efficient and safer solution is to manipulate the delimited string into a format acceptable to the IN function. One option is to use the CHARINDEX() function to check if each value in the delimited string matches a column value in the table:

<code class="language-sql">DECLARE @Ids varchar(50) = ',1,2,3,5,4,6,7,98,234,'

SELECT * 
FROM sometable
WHERE CHARINDEX(','+CAST(tableid AS VARCHAR(8000))+',', @Ids) > 0</code>

This method does not require dynamic SQL, thus balancing security and performance advantages.

Simple solution

For those looking for a less elegant but more convenient solution, there is a simple trick:

<code class="language-sql">DECLARE @Ids varchar(50) = ',1,2,3,5,4,6,7,98,234,'

SELECT * 
FROM sometable
WHERE ',' + CONVERT(VARCHAR, tableid) + ',' LIKE '%,' + @Ids + '%';</code>

While this method is not as efficient as the CHARINDEX() method, it provides a quick and easy alternative.

The above is the detailed content of How to Efficiently Pass Comma-Delimited Values to a SQL Server IN Function?. For more information, please follow other related articles on the PHP Chinese website!