/* 跟踞指定的ID系列来获得这些ID的名称 (id之间用,分开) */
CREATE FUNCTION IdsToTexts(@ids varchar(100))
RETURNS nvarchar(4000) AS
BEGIN
If(@Ids is null or @Ids='') Return ''
Declare @Texts nvarchar(4000) --返回的名称系列
Declare @idTable table(colValue varchar(20)) --ID表
Declare @curId varchar(20) --当前ID
Declare @curName varchar(50) --当前名称
Set @Texts = ''
Set @curId = ''
Insert Into @idTable Select * from dbo.Split(@Ids,',') --取得ID表
/* 创建游标逐行读数 */
Declare newtable Cursor local forward_only static read_only type_warning
For Select * From @idTable
open newtable
fetch next from newtable into @curId
While @@fetch_status=0
Begin
--set @Texts = @Texts + @curId + ','
Select @curName=Entry_tw from PE_Entry Where EntryId=@curId
set @Texts = @texts + @curName + ','
fetch next from newtable into @curId
End
close newtable
deallocate newtable
Return @Texts
END
/* 将指定的字符串以指定的分割符分割,返回分割后的表 */
CREATE FUNCTION Split(@strSplit varchar(8000),@separate varchar(10))
RETURNS @returnTable table(colValue varchar(20)) AS
BEGIN
Declare @curSplit varchar(20)
Declare @startIndex int
Declare @endIndex int
set @endIndex = 0
if Right(@strSplit,len(@separate))<>@separate set @strSplit = @strSplit + @separate
set @startIndex = CharIndex(@separate,@strSplit,@endIndex)
While @endIndex<@startIndex
Begin
set @curSplit = SubString(@strSplit,@endIndex,@startIndex-@endIndex)
set @endIndex = @startIndex + 1
set @startIndex = CharIndex(@separate,@strSplit,@endIndex)
Insert Into @returnTable Values(@curSplit)
End
Return
END