IF (EXISTS(Select * from sys.sysprocedure where LCase(proc_name) = LCase('GetInvoices')))
THEN Drop procedure GetInvoices
END IF

GO

create function DBA.GetInvoices( in @GNo integer,in @Mnth integer,in @Yr integer ) 
returns varchar(250)
begin
  declare rez varchar(250);
  declare _egn varchar(18);
  declare _isEgnEnch smallint;
  set rez = '';
  select egn,isEgnEnch into _egn,_isEgnEnch from Guests where GuestNum = @GNo;
  for f as curs scroll cursor for
    select distinct A.InvoiceNo as InvNo,(select GrandTotal from Invoices where InvoiceNo = A.InvoiceNo) as Total from Articles as A,SvoRedoveStn as SVS1,SvoRedoveStn as SVS2
      where A.RegNo = any(select RegNum from Book where WhoPays = any(select GuestNum from Guests where egn = _egn and isEgnEnch = _isEgnEnch))
      and SVS1.Svoistvo = 1 and SVS2.Svoistvo = 2 and SVS1.Stoinost = @Mnth and SVS2.Stoinost = @Yr
      and A.ArticleNo = SVS1.ArticleNo and A.InvoiceNo = SVS1.InvoiceNo
      and A.ArticleNo = SVS2.ArticleNo and A.InvoiceNo = SVS2.InvoiceNo do
    set rez = rez || (select Inv1.ExternalNo from Invoices as Inv1 where Inv1.InvoiceNo = InvNo) || '(' || FormatNum(Total,'0.00') || '), ' end for;
  set rez = substr(rez,0,length(rez)-2);
  return rez
end