Wednesday, June 8, 2011

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

No comments:

Post a Comment