极念网欢迎您!
SQL Server如何在IN条件时使用参数查询
作者:翅膀的初衷来源:本站原创发布时间:2015/1/16 21:59:05查看数:1990

常规情况下,如果使用IN条件查询数据时,应避免使用参数化查询,类于如下代码

select * from my_user where userid in (1,2,3)

您应该直接拼成上图格式,而不是这么写:

set @idstr = '1,2,3' select * from my_user where userid in (@idstr)

原因是系统会认为你要的代码是 userid in ('1,2,3') 而不是你预期的 userid in (1,2,3)

但是有时,我们却遇某些特殊情况,必须在有参数的情况下使用in,怎么办?

场景一:我有一个存储过程,数据传进来本来就是参数,而我也必须使用in来查询。

很多朋友图简单,直接使用字符串相拼,再使用exec 执行。这方法是可行的,但是却存在安全漏洞,而且有某些特殊情况下,是无法使用exec的。

场景二:我有一个存储过程,数据传进来本来就是参数,而我也必须使用in来查询。而且业务逻辑很复杂,要使用使局部临时表,在包含in的语句中也要进行临时表操作,之外也要进行临时表操作。那么在这种情况下,如果你直接exec,你会发现exec里面的SQL语句,无法访问外面的语句创建的临时表。

还好,在sql server 2008中,开始支持表值函数,我们可以自己分隔字符,通过表值函数,返回数据表即可。

定义

表值函数返回 table。对于内嵌表值函数,没有函数主体;表是单个 SELECT 语句的结果集。对于多语句表值函数,在 BEGIN...END 块中定义的函数主体包含 TRANSACT-SQL 语句,这些语句可生成行并将行插入将返回的表中。

语法

CREATE FUNCTION [ owner_name.] function_name ( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] ) RETURNS table [ WITH {Encryption | Schemabinding }] [ AS ] RETURN( select 语句)

那么,在场景二的T-SQL,那么就可以这么写了:先创建一个表值函数

Create FUNCTION Split ( @SplitString nvarchar(max), @Separator char(1)=',' ) RETURNS @SplitStringsTable TABLE ( [value] varchar(max) ) AS BEGIN DECLARE @CurrentIndex int; DECLARE @NextIndex int; DECLARE @ReturnText nvarchar(max); SELECT @CurrentIndex=1; WHILE(@CurrentIndex<=len(@SplitString)) BEGIN SELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex); IF(@NextIndex=0 OR @NextIndex IS NULL) SELECT @NextIndex=len(@SplitString)+1; SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex); INSERT INTO @SplitStringsTable([value]) VALUES(@ReturnText); SELECT @CurrentIndex=@NextIndex+1; END RETURN; END

然后调用它就可以了

select * from jn_user where userid in (SELECT value FROM Split(@idsrt))