使用Dapper参数化查询(三) IN 查询
表值函数返回 table。
对于内嵌表值函数,没有函数主体;
表是单个 SELECT 语句的结果集。对于多语句表值函数,在 BEGIN...END 块中定义的函数主体包含 TRANSACT-SQL 语句,这些语句可生成行并将行插入将返回的表中。
先创建一个表值函数
-- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: in 参数化查询 -- ============================================= CREATE FUNCTION [dbo].[Split] ( @SplitString varchar(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 GO
然后调用它就可以了
select * from jn_user where userid in (SELECT value FROM Split(@idsrt))
关闭