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