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

Avoiding Dynamic SQL: Passing Comma-Separated Values to SQL Server's IN Clause

Directly passing comma-separated strings to SQL Server's IN function often leads to type conversion errors. For example:

DECLARE @Ids varchar(50);
SET @Ids = '1,2,3,5,4,6,7,98,234';

SELECT * FROM sometable
WHERE tableid IN (@Ids);

This will produce a "Conversion failed" error because the IN clause expects individual integer values, not a string.

A Dynamic SQL-Free Solution

A robust alternative, avoiding the pitfalls of dynamic SQL, involves leveraging the CHARINDEX function:

DECLARE @Ids varchar(50);
SET @Ids = ',1,2,3,5,4,6,7,98,234,'; -- Note the leading and trailing commas

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

By adding commas before and after both the input string @Ids and the cast tableid, we ensure that each individual value is correctly identified within the comma-delimited string. The CHARINDEX function then efficiently checks for the presence of each tableid within the modified @Ids string. This effectively mimics the behavior of the IN clause without requiring dynamic SQL.

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