在Sql中我们经常会用到in

普遍的写法为

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

通过函数写法为:

IF EXISTS ( SELECT *

FROM sys.objects

WHERE object_id = OBJECT_ID(N'[dbo].[ArrayToTable]')

AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ) )

DROP FUNCTION [dbo].[ArrayToTable]

GO

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