How to Pass a Comma-Delimited String to SQL Server's IN Function Without Dynamic SQL?

Avoiding Dynamic SQL: Handling Comma-Separated Values in SQL Server's IN Clause

Directly passing a comma-delimited string to SQL Server's IN function often leads to type conversion errors. Let's examine a common scenario:

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

SELECT * 
FROM sometable 
WHERE tableid IN (@Ids);</code>

This will result in a conversion error because the IN clause expects individual integer values, not a string. To circumvent this without using dynamic SQL, a more robust method is to employ the CHARINDEX function:

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

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

This improved query cleverly adds commas to both ends of the @Ids string. CHARINDEX then searches for a comma-delimited version of tableid within the modified @Ids string. This ensures exact matches and prevents partial matches that could lead to incorrect results. Only rows where the tableid is found within the comma-separated list will be returned.

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