In SQL server query conditions, can variables be used in in? If you can, are there any places or restrictions that need attention? Before answering this question, let’s take a look at this example
As shown below, if there is only one value in the query condition, the SQL is normal.
What if we want to enter multiple values in the query condition in? If there is such a requirement, a variable contains the values of B and C. now use ‘B | C’’As a condition, split it into variables’ B ‘and’ C ‘. To find out the records of name = B and name = C, as shown in the following screenshot, SQL is not exactly what you want“Envision / envision”Instead of finding out the corresponding record, I found out the record with id = 5
The following SQL is the same result.
Why did this happen? After checking a large number of official documents, I didn’t see the introduction and explanation of this problem. If we have to explain the above phenomenon, it is becauseSELECT * FROM TEST WHERE name IN (@name2 ); It turns intoSELECT * FROM TEST WHERE name =@name2; That is to say, the above SQL will not follow what you want“imagine”The logic operation of. It’s a transformation. Why such a transformation? Of course, this is also a conjecture. The example constructed above is also to verify this conjecture. In addition, the parameter list of the two SQL actual execution plans also confirms this conjecture. If the execution plan resolves to the desired result, the parameter list should be ‘B’ and‘c’
1: Using dynamic SQL
There seems to be nothing to say about using dynamic SQL to solve problems, as shown in the following example:
2: Using temporary tables or table variables
Taking this example as an example, it is OK to split a string, put it into a temporary table or table variable, and then associate it with a table or use a subquery in an in.
3: With string_ SPLIT()
be careful：STRING_ Split function is only supported in higher version, and it is supported in some versions of SQL Server 2017 or SQL Server 2016.
4: Using XML function to solve the problem