UFN_COMBINATION_GETPRICE

CREATE function dbo.UFN_COMBINATION_GETPRICE
(
  @COMBINATIONID uniqueidentifier,
  @COMBINATIONPRICETYPEID uniqueidentifier
)
returns money
with execute as caller
as begin

        -- Total program groups in the combination

        declare @TOTALPROGRAMGROUPS int
        select @TOTALPROGRAMGROUPS = count(ID)
          from dbo.PROGRAMGROUP
          where COMBINATIONID = @COMBINATIONID

declare @PRICE money

select @PRICE = 
    case count(PROGRAMGROUPPRICE.ID)
              when @TOTALPROGRAMGROUPS then sum(PROGRAMGROUPPRICE.FACEPRICE) 
              else null
          end 

from dbo.COMBINATIONPRICETYPE
left join dbo.PROGRAMGROUPPRICE on COMBINATIONPRICETYPE.ID = PROGRAMGROUPPRICE.COMBINATIONPRICETYPEID 
where COMBINATIONPRICETYPE.COMBINATIONID = @COMBINATIONID
and COMBINATIONPRICETYPE.ID = @COMBINATIONPRICETYPEID

return @PRICE

end