Инлайн-функция

Create Function dbo.hotel_function (@org varchar(max))
returns table
return
select hotel.name, stuff((select ', ' + fio  from participant t
             for xml path('')), 1,1,'')
             from hotel where hotel.id_hotel=(select id_hotel from participant where 
 
id_organization = 
             (select id_organization from organization where name = 'Волгоградская медицинская академия'))

GO
Create function dbo.hotel_function (@org varchar(max))
returns table
as
Begin
declare @tbl table (rowId int identity(1,1),hotel_id int, fio_list varchar(max) )
 
Insert @tbl select id_hotel from participant where id_organization = 
             (select id_organization from organization where name = 'Волгоградская медицинская академия')
return @tbl
 
 end

Insert @tbl select id_hotel from participant where id_organization = 
             (select id_organization from organization where name = 'Волгоградская медицинская академия')

Insert @tbl (hotel_id) select id_hotel from participant where id_organization = 
             (select id_organization from organization where name = 'Волгоградская медицинская академия')

Insert @tbl select id_hotel, NULL from participant where id_organization = 
             (select id_organization from organization where name = 'Волгоградская медицинская академия')

select h.name,
   stuff((
      select ','+fio 
      from participant 
      where id_hotel=h.id_hotel 
      order by fio 
      for xml path('')
   ),1,1,'') list
from (
   select distinct h.*
   from organization o
   join participant p on p.id_organization=o.id_organization
   join hotel h on h.id_hotel=p.id_hotel
   where o.name='Волгоградская медицинская академия'
)h

select * from organization

where o.name='Волгоградская медицинская академия'

Create function dbo.hotel_function (@org varchar(max))
returns table
 
return select h.name,
   stuff((
      select ','+fio 
      from participant 
      where id_hotel=h.id_hotel AND id_organization = 
      (select id_organization from organization where name = 'Астраханский государственный технический университет')
      order by fio 
      for xml path('')
   ),1,1,'') list
from (
   select distinct h.*
   from organization o
  inner join participant p on p.id_organization=o.id_organization
  inner join hotel h on h.id_hotel=p.id_hotel
  where o.name='Астраханский государственный технический университет'
)h

AND id_organization = 
      (select id_organization from organization where name = 'Астраханский государственный технический университет')

declare @org varchar(max)--='Org1'
;with 
   hotel as (select * from (values (1,'hotel1'), (2,'hotel2'), (3,'hotel3'))t(id_hotel,name)),
   organization as (select * from (values(1,'Org1'),(2,'Org2'))t(id_organization,name)),
   participant as (select * from (values
      (1,'Par1',1),(1,'Par2',1),(1,'Par3',2),(2,'Par4',1))t(id_organization, fio, id_hotel))
--create function dbo.hotel_function (@org varchar(max)) returns table return 
select 
   id_organization,
   (select name from organization where id_organization=d.id_organization)name_organization,
   id_hotel,
   (select name from hotel h where id_hotel=d.id_hotel)name_hotel,
   stuff((
      select ','+fio 
      from participant
      where id_hotel=d.id_hotel and id_organization=d.id_organization
      order by fio 
      for xml path('')
   ),1,1,'') list_participants
from (
  select distinct id_hotel,id_organization
  from participant 
  where id_organization in 
    (select id_organization from organization where @org is null or name like @org)
)d