本文轉(zhuǎn)載自微信公眾號(hào)「DBA閑思雜想錄」，作者瀟湘隱者 。轉(zhuǎn)載本文請聯(lián)系DBA閑思雜想錄公眾號(hào)。   

在SQL Server的查詢條件中，能否在IN里面使用變量呢?如果可以的話，有沒有需要注意的地方或一些限制呢?在回答這個(gè)問題前，我們先來看看這個(gè)例子：

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 

如下所示，如果查詢條件里面，變量只有一個(gè)值，此時(shí)SQL是正常的。

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 

如果我們想在查詢條件IN里面輸入多個(gè)值呢?假如有這樣的一個(gè)需求，一個(gè)變量里面包含b和c的值，現(xiàn)在用'b|c’作為條件傳入，對其進(jìn)行拆分為變量'b'和'c', 想查出name=b 和name=c的記錄，如下截圖所示，SQL其實(shí)并沒有按你所“設(shè)想/預(yù)想”的查出對應(yīng)記錄，而是將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 + '''')); 

下面這個(gè)SQL也是同樣的結(jié)果。

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

為什么出現(xiàn)了這樣的結(jié)果呢?查了大量的官方文檔，沒有看到關(guān)于這個(gè)問題的介紹和解釋。如果一定要解釋上面現(xiàn)象的情況的話，那么是因?yàn)镾ELECT * FROM TEST WHERE name IN (@name2 ); 其實(shí)轉(zhuǎn)化為了SELECT * FROM TEST WHERE name =@name2; 也就是說，上面SQL并不會(huì)按你所“設(shè)想”的邏輯運(yùn)算。而是做了一個(gè)轉(zhuǎn)換，為什么說是這樣的一個(gè)轉(zhuǎn)換呢?當(dāng)然這也是一個(gè)猜想，上面構(gòu)造的例子也是為了側(cè)面驗(yàn)證這個(gè)猜想，另外，上面兩個(gè)SQL實(shí)際執(zhí)行計(jì)劃的參數(shù)列表(Parameter List)也側(cè)面印證了這個(gè)猜想(如下截圖所示)。如果執(zhí)行計(jì)劃解析成我們想要的結(jié)果，那么Parameter List應(yīng)該是'b' 和‘c'

解決方案：

1：使用動(dòng)態(tài)SQL

使用動(dòng)態(tài)SQL解決問題，似乎沒啥好說的，如下例子所示：

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; 

2：使用臨時(shí)表或表變量

以這個(gè)例子來說，就是將字符串拆分，放入臨時(shí)表或表變量，然后關(guān)聯(lián)表也好，在IN里面使用子查詢也OK。

3：借助STRING_SPLIT()

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

注意：STRING_SPLIT函數(shù)只有較高版本才支持，SQL Server 2017或SQL Server 2016部分版本支持。

4：借助XML函數(shù)來解決問題

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