查询时将数据表中以;分隔的用户ID字段转换成以;分隔的用户名（原创）（备忘）
View Code
1 CREATE FUNCTION [dbo].[TransUserIdsToNames] ( @ids NVARCHAR(1024) ) --以；分隔的用户ID字符串
2 RETURNS VARCHAR(50)
3 AS
4 BEGIN
5 DECLARE @returns NVARCHAR(1024)
6 DECLARE @t TABLE ( id VARCHAR(10) )
7 DECLARE @i INT
8 DECLARE @id NVARCHAR(10)
9 SET @i = CHARINDEX(';', @ids)
10 WHILE ( LEN(@ids) > 0 )
11 BEGIN
12 IF @i = 0
13 SET @id = @ids
14 ELSE
15 SET @id = LEFT(@ids, @i - 1)
16 INSERT INTO @t
17 ( id )
18 VALUES ( @id )
19 IF @i = 0
20 SET @ids = ''
21 ELSE
22 SET @ids = RIGHT(@ids, LEN(@ids) - @i)
23 SET @i = CHARINDEX(';', @ids)
24 END
25
26 SELECT @returns = STUFF(( SELECT ',' + ( SELECT UserName
27 FROM 用户表
28 WHERE UserID = t.Id
29 )
30 FROM @t t
31 FOR
32 XML PATH('')
33 ), 1, 1, '')
34
35 RETURN @returns
36 END
发贴备忘
