SQL Server查询条件IN中能否使用变量的示例详解_Sql Server

SQL Server查询条件IN中能否使用变量的示例详解

2021-02-21 16:54潇湘隐者 Sql Server

这篇文章主要介绍了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

SQL Server查询条件IN中能否使用变量的示例详解

如下所示，如果查询条件里面，变量只有一个值，此时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 + ''''));

SQL Server查询条件IN中能否使用变量的示例详解

下面这个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'

SQL Server查询条件IN中能否使用变量的示例详解

解决方案：

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;

SQL Server查询条件IN中能否使用变量的示例详解

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, '|'))

SQL Server查询条件IN中能否使用变量的示例详解

注意：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 ) );