SQL Server解惑——查询条件IN中能否使用变量

运维 数据库运维
在SQL Server的查询条件中,能否在IN里面使用变量呢?如果可以的话,有没有需要注意的地方或一些限制呢?

本文转载自微信公众号「DBA闲思杂想录」,作者潇湘隐者 。转载本文请联系DBA闲思杂想录公众号。   

在SQL Server的查询条件中,能否在IN里面使用变量呢?如果可以的话,有没有需要注意的地方或一些限制呢?在回答这个问题前,我们先来看看这个例子:

  1. IF EXISTS (SELECT 1 FROM sys.objects WHERE name='TEST' AND type='U'
  2. BEGIN 
  3.  DROP TABLE TEST; 
  4. END 
  5. GO 
  6. CREATE TABLE TEST ( ID INTNAME VARCHAR(16) ); 
  7. GO 
  8.  
  9. INSERT INTO dbo.TEST 
  10. SELECT 1, 'a'  UNION ALL 
  11. SELECT 2, 'b'  UNION ALL 
  12. SELECT 3, 'c'  UNION ALL 
  13. SELECT 4, 'a,b'UNION ALL 
  14. SELECT 5, '''b'',''c''' UNION ALL 
  15. SELECT 6, '''b'
  16. GO 

如下所示,如果查询条件里面,变量只有一个值,此时SQL是正常的。

  1. DECLARE @name VARCHAR(16); 
  2. SET @name='a'
  3.  
  4. SELECT * FROM TEST WHERE name IN (@name); 
  5. GO 
  6.  
  7. DECLARE @name VARCHAR(16); 
  8. SET @name='a,b'
  9.  
  10. SELECT * FROM TEST WHERE name IN (@name); 
  11. GO 

如果我们想在查询条件IN里面输入多个值呢?假如有这样的一个需求,一个变量里面包含b和c的值,现在用'b|c’作为条件传入,对其进行拆分为变量'b'和'c', 想查出name=b 和name=c的记录,如下截图所示,SQL其实并没有按你所“设想/预想”的查出对应记录,而是将ID=5的记录查出来了

  1. DECLARE @name1 VARCHAR(16); 
  2. DECLARE @name2 VARCHAR(16); 
  3. SET @name1='b|c'
  4. SET @name2=REPLACE(@name1,'|',''','''
  5. SELECT @name2 
  6.  
  7. SELECT * FROM TEST WHERE name IN (('''' + @name2 + '''')); 

下面这个SQL也是同样的结果。

  1. DECLARE @name1 VARCHAR(16); 
  2. DECLARE @name2 VARCHAR(16); 
  3. SET @name1='b|c'
  4. SET @name2='''' + REPLACE(@name1,'|',''',''') +'''' 
  5. SELECT @name2 
  6.  
  7. 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'

解决方案:

1:使用动态SQL

使用动态SQL解决问题,似乎没啥好说的,如下例子所示:

  1. DECLARE @sql_cmd NVARCHAR(max); 
  2. DECLARE @name VARCHAR(16); 
  3.  
  4. SET @name='b|c'
  5. SET @sql_cmd='SELECT * FROM TEST WHERE name IN (''' + REPLACE(@name,'|',''',''') +''');' 
  6.  
  7. EXEC sp_executesql @sql_cmd; 

2:使用临时表或表变量

以这个例子来说,就是将字符串拆分,放入临时表或表变量,然后关联表也好,在IN里面使用子查询也OK。

3:借助STRING_SPLIT()

  1. DECLARE @name VARCHAR(16); 
  2.  
  3. SET @name='b|c'
  4. SELECT *FROM  test WHERE name IN (SELECT value FROM STRING_SPLIT(@name'|')) 

注意:STRING_SPLIT函数只有较高版本才支持,SQL Server 2017或SQL Server 2016部分版本支持。

4:借助XML函数来解决问题

  1. DECLARE @name VARCHAR(16); 
  2. DECLARE @xml_para XML; 
  3.  
  4. SET @name = 'b|c'
  5. SET @xml_para = CAST(( '<A>' + REPLACE(@name'|''</A><A>') + '</A>' ) AS XML); 
  6.  
  7.  
  8. SELECT  * 
  9. FROM    dbo.TEST 
  10. WHERE   NAME IN ( SELECT    A.value('.''varchar(max)'AS [Column
  11.                   FROM      @xml_para.nodes('A'AS FN ( A ) ); 

 

责任编辑:武晓燕 来源: DBA闲思杂想录
相关推荐

2021-02-03 08:01:35

SQLServerLIKE

2010-11-09 15:18:37

SQL Server多

2010-10-21 11:10:57

SQL Server查

2010-10-21 10:28:13

SQL Server查

2010-11-09 13:28:13

SQL SERVER查

2011-07-04 14:28:18

SQL Server分区

2021-01-07 09:20:08

SQL字符串Server

2010-07-16 08:50:00

SQL Server表

2010-09-14 16:36:34

SQL SERVER查

2010-06-29 17:52:02

SQL Server嵌

2011-03-29 13:22:07

SQL Server临时表表变量

2012-07-11 23:10:49

SQL Server数据库

2010-09-03 14:56:12

SQLSELECT语句

2010-09-06 17:11:14

SQL函数

2011-08-14 23:26:30

激光打印机常见问题

2010-07-22 16:02:29

2021-12-14 07:05:00

SQL语句数据库

2010-10-21 10:42:30

SQL Server查

2011-08-12 09:14:08

SQL Server创建数据库创建表

2011-03-21 10:26:10

SQL Server视图管理
点赞
收藏

51CTO技术栈公众号