CREATE FUNCTION DBO.Inlist (@list varchar(8000)) RETURNS @tbl TABLE (val int not null, Ord int not null) AS BEGIN Declare @index int, @pos int, @str varchar(8000), @num int Set @pos = 1 Set @index = 1 While @index > 0 Begin set @index = charindex(',', @list, @pos) if @index > 0 Set @str = substring(@list, @pos, @index - @pos) Else Set @str = substring(@list, @pos, Len(@list)) Set @str = ltrim(rtrim(@str)) Set @num = cast(@str as integer) Insert @tbl (val,ord) values (@num,@pos) Set @pos = @index + 1 End Return EndExample Call:
declare @tcDeptList varchar(200) set @tcDeptList = '123,124,175,198' SELECT dbo.meetings.begintime, dbo.meetings.endtime, dbo.meetings.sdatetime dbo.coRooms.Roomname FROM dbo.meetings (nolock) INNER JOIN dbo.corooms (nolock) ON dbo.meetings.roomid = dbo.corooms.roomid where dbo.meetings.RoomID in (select val from dbo.inlist(@tcDeptList))