SQL Server puzzle: can variables be used in query condition in

Time:2021-3-6

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

 

IF EXISTS (SELECT 1 FROM sys.objects WHERE name='TEST' AND type='U')
BEGIN
    DROP TABLE TEST;
END
GO
CREATE TABLE TEST ( ID INT, NAME VARCHAR(16) );
GO
 
INSERT INTO dbo.TEST
SELECT 1, 'a'  UNION ALL
SELECT 2, 'b'  UNION ALL
SELECT 3, 'c'  UNION ALL
SELECT 4, 'a,b'UNION ALL
SELECT 5, '''b'',''c''' UNION ALL
SELECT 6, '''b';
GO

 

clip_image001

 

As shown below, if there is only one value in the query condition, the SQL is normal. 

DECLARE @name VARCHAR(16);
SET @name='a';
 
SELECT * FROM TEST WHERE name IN (@name);
GO
 
DECLARE @name VARCHAR(16);
SET @name='a,b';
 
SELECT * FROM TEST WHERE name IN (@name);
GO

 

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 wantEnvision / envisionInstead of finding out the corresponding record, I found out the record with id = 5

 

DECLARE @name1 VARCHAR(16);
DECLARE @name2 VARCHAR(16);
SET @name1='b|c';
SET @name2=REPLACE(@name1,'|',''',''')
SELECT @name2
 
SELECT * FROM TEST WHERE name IN (('''' + @name2 + ''''));

 

 

clip_image002

 

The following SQL is the same result. 

 

DECLARE @name1 VARCHAR(16);
DECLARE @name2 VARCHAR(16);
SET @name1='b|c';
SET @name2='''' + REPLACE(@name1,'|',''',''') +''''
SELECT @name2
 
SELECT * FROM TEST WHERE name IN (@name2 );

 

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 wantimagineThe 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’ andc’

 

 

 

clip_image003

 

clip_image004

 

 

 

Solution:

 

1: Using dynamic SQL

 

There seems to be nothing to say about using dynamic SQL to solve problems, as shown in the following example:

 

DECLARE @sql_cmd NVARCHAR(max);
DECLARE @name VARCHAR(16);
 
SET @name='b|c';
SET @sql_cmd='SELECT * FROM TEST WHERE name IN (''' + REPLACE(@name,'|',''',''') +''');'
 
EXEC sp_executesql @sql_cmd;

 

clip_image005

  

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() 

DECLARE @name VARCHAR(16);
 
SET @name='b|c';
SELECT *FROM  test WHERE name IN (SELECT value FROM STRING_SPLIT(@name, '|'))

 

clip_image006

 

be carefulSTRING_ 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 

DECLARE @name VARCHAR(16);
DECLARE @xml_para XML;
 
SET @name = 'b|c';
SET @xml_para = CAST(( '<A>' + REPLACE(@name, '|', '</A><A>') + '</A>' ) AS XML);
 
 
SELECT  *
FROM    dbo.TEST
WHERE   NAME IN ( SELECT    A.value('.', 'varchar(max)') AS [Column]
                  FROM      @xml_para.nodes('A') AS FN ( A ) );

 

clip_image007