SQL Function for getting multiple user name from multiple user id that are in a single string

ALTER function [dbo].[fn_CombineTeacherClassTestSummary]
(
@TeacherId varchar(100)
)
--select[dbo].[fn_CombineTeacherClassTestSummary]('T00176,T00181')
returns varchar(400)
as
begin
declare @Name varchar(400)='',
@sDelimiter VARCHAR(400) = ',',
@sInputList VARCHAR(200),
@sItem VARCHAR(400),
@FetchName varchar(100)
SET @sInputList=@TeacherId
WHILE CHARINDEX(@sDelimiter,@sInputList,0) 0
BEGIN
SELECT
@sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))

IF LEN(@sItem) > 0
begin
SELECT @FetchName=UserName From T_UserSetup WHERE UserId=@sItem
SET @Name=@Name+@FetchName+', '
end
END
IF LEN(@sInputList) > 0
begin
SELECT @FetchName=UserName From T_UserSetup WHERE UserId=@sInputList -- Put the last item in
SET @Name=@Name+@FetchName
end
return @Name
end

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s