Comma Separated value in SQL

create function [dbo].[commaSeperateValue]

(

@inttypeid int,

@intmemberid int,

@parametertypeid int

)

returns varchar(1000)

as

begin

declare @Values varchar(1000)

if @inttypeid = 1 -- buiding types

select @Values = Stuff((SELECT DISTINCT ', ' + vchBuildingType AS [text()]

FROM member_building_type mbt inner join building_type bt on bt.intbuildingtypeid=mbt.intbuildingtypeid

and bt.bitIsActive=1 and mbt.intmemberid = @intmemberid and mbt.intBuildingTypeId = (case when @parametertypeid = 0 then mbt.intBuildingTypeId else @parametertypeid end)

FOR XML PATH ('')),1,1,'')

if @inttypeid = 2 -- Role types

select @Values = Stuff((SELECT DISTINCT ', ' + vchRoleName AS [text()]

FROM email_template_roles mbt inner join [role] bt on bt.introleId=mbt.introleId

and bt.bitActive=1

where mbt.intTemplateId=@intmemberid

FOR XML PATH ('')),1,1,'')

if @inttypeid = 3 -- Services

select @Values = Stuff((SELECT DISTINCT ', ' + vchServiceType AS [text()]

FROM member_setting_zipcode msz

inner join service s on s.intServiceId=msz.intserviceid

inner join service_type st on st.intServiceTypeId=s.intServiceId

and msz.intserviceid = (case when @parametertypeid = 0 then msz.intserviceid else @parametertypeid end)

and msz.intmemberid=@intmemberid

FOR XML PATH ('')),1,1,'')

return @Values

end

Calling :

select dbo.lookup_types_commaSeperate (2,4,0)

Output:

Admin, Chapter President,Inspector,Contractor