查询时将数据表中以;分隔的用户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 

发贴备忘

转载于:https://www.cnblogs.com/garin-yea/archive/2011/12/14/2287580.html