Why Does Using a VARCHAR Variable in an SQL Server IN Clause Cause a Conversion Error?

SQL Server IN Clause and VARCHAR Variables: A Troubleshooting Guide

Problem:

The following SQL Server code generates a conversion error:

<code class="language-sql">DECLARE @ExcludedList VARCHAR(MAX)
SET @ExcludedList = '3, 4, 22' -- Note: Strings are now enclosed in single quotes

SELECT * FROM A WHERE Id NOT IN (@ExcludedList)</code>

Explanation of the Error:

The IN clause expects a comma-separated list of values of the same data type as the column being compared (in this case, Id, which is presumably an integer). The variable @ExcludedList is a VARCHAR(MAX), containing a string. SQL Server attempts to implicitly convert the entire string to an integer, leading to a failure because the string includes commas.

Solution:

Avoid using a string variable directly within the IN clause. Instead, use a table-valued approach:

<code class="language-sql">DECLARE @ExcludedList VARCHAR(MAX)
SET @ExcludedList = '3, 4, 22'

DECLARE @IntExcludedList TABLE (ID INT);
INSERT INTO @IntExcludedList (ID)
SELECT value FROM STRING_SPLIT(@ExcludedList, ',');

SELECT * FROM A WHERE Id NOT IN (SELECT ID FROM @IntExcludedList);</code>

This improved code utilizes STRING_SPLIT, a built-in function that efficiently splits the comma-separated string into individual rows. Each row's value is then inserted into the integer table variable @IntExcludedList, allowing for a correct comparison with the Id column. This method prevents implicit conversions and ensures the IN clause works as intended. Note the use of single quotes around the string values in @ExcludedList.

This approach is more efficient and readable than the original solution that used SUBSTRING and PATINDEX. It leverages SQL Server's built-in string manipulation capabilities for optimal performance.

The above is the detailed content of Why Does Using a VARCHAR Variable in an SQL Server IN Clause Cause a Conversion Error?. For more information, please follow other related articles on the PHP Chinese website!