巧用函数，使Sql中in的用法更多变

来源：互联网发布：手机怎么举报淘宝卖家编辑：程序博客网时间：2024/05/17 02:59

在Sql中我们经常会用到in

普遍的写法为

where xx in ('1','2','3')

通过函数写法为:

IF EXISTS ( SELECT *FROM sys.objectsWHERE object_id = OBJECT_ID(N'[dbo].[ArrayToTable]') AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ) ) DROP FUNCTION [dbo].[ArrayToTable]GOcreate function ArrayToTable(@arrayList varchar(2000),@split varchar(2))returns @result table(subscript int,value char(200))as begin declare @i int, @index int set @i = 0 set @index = charindex(@split,@arrayList) while(@index <> 0) begin insert into @result(subscript,value) values(@i,substring(@arrayList,1,@index-1)) set @arrayList = stuff(@arrayList,1,@index,'') set @index = charindex(@split,@arrayList) set @i = @i+1 end insert into @result(subscript,value) values(@i,@arrayList) return end

declare @lineData varchar(200)
set @lineData='1,2,3,4'

where (@lineData is null or XX in (select value from dbo.ArrayToTable(@lineData,',')))