SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE   FUNCTION CombineGroupNames ( @ResourceID int )
RETURNS VarChar(2000)

AS
BEGIN

Declare @list varchar(2000) 

set @list = ''

select @list = case @list when null then groupname else @list + groupname + '|' end 
         FROM Resource R
   INNER JOIN ResourceSecurityGroup RG
           ON R.ResourceID = RG.ResourceID
   INNER JOIN SecurityGroup G
           ON G.GroupID = RG.GroupID
        WHERE R.ResourceID = @ResourceID

if Len(rtrim(@list))  > 0
	begin
		if charindex('|',@list,Len(@list)-1) > 0			
			select @list = Substring(@list,0,len(@list))
	end

return @list
End

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO