SQL Server解惑——查詢條件IN中能否使用變數-有解無憂

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

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

如下所示，如果查詢條件里面，變數只有一個值，此時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里面輸入多個值呢？假如有這樣的一個需求，一個變數里面包含b和c的值，現在用'b|c’作為條件傳入，對其進行拆分為變數'b'和'c', 想查出name=b 和name=c的記錄，如下截圖所示，SQL其實并沒有按你所“設想/預想”的查出對應記錄，而是將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

下面這個SQL也是同樣的結果，

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

為什么出現了這樣的結果呢？查了大量的官方檔案，沒有看到關于這個問題的介紹和解釋，如果一定要解釋上面現象的情況的話，那么是因為SELECT * FROM TEST WHERE name IN (@name2 ); 其實轉化為了SELECT * FROM TEST WHERE name =@name2; 也就是說，上面SQL并不會按你所“設想”的邏輯運算，而是做了一個轉換，為什么說是這樣的一個轉換呢？當然這也是一個猜想，上面構造的例子也是為了側面驗證這個猜想，另外，上面兩個SQL實際執行計劃的引數串列（Parameter List）也側面印證了這個猜想，如果執行計劃決議成我們想要的結果，那么Parameter List應該是'b' 和‘c'

clip_image003

clip_image004

解決方案：

1：使用動態SQL

使用動態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;

clip_image005

2：使用臨時表或表變數

以這個例子來說，就是將字串拆分，放入臨時表或表變數，然后關聯表也好，在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, '|'))

clip_image006

注意：STRING_SPLIT函式只有較高版本才支持，SQL Server 2017或SQL Server 2016部分版本支持，

4：借助XML函式來解決問題

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

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/250587.html

標籤：其他

上一篇：redis-集群（1）

下一篇：實用sql整理