
CREATE procedure DBO.productdayprice @arrival varchar(8) = '', @departure varchar(8) = '', @adults int = 1, @children1 int = 0, @children2 int = 0, @productname varchar(100) = '', @productnumber int = 0, @date varchar(8) = '', @roomtypenumber int = 0 as
SET NOCOUNT ON

declare @sql varchar(8000)
declare @result money
declare @datestamp datetime
declare @arrivaldate datetime
declare @departuredate datetime
declare @adultfield varchar(25)
declare @children1field varchar(25)
declare @children2field varchar(25)
declare @allfields varchar(800)
declare @addonadultsfields varchar(50)
declare @addonchildren1fields varchar(50)
declare @addonchildren2fields varchar(50)

set @allfields = ''
set @addonadultsfields = ''
set @addonchildren1fields = ''
set @addonchildren2fields = ''
set @datestamp = convert(datetime, @date, 112)
set @arrivaldate = convert(datetime, @arrival, 112)
set @departuredate = convert(datetime, @departure, 112)

if @productnumber = 0 
  select @productnumber = productnumber from products WITH (nolock) where productname = @productname

set @result = 0

if @adults > 9 set @adults = 9
if @children1 > 9 set @children1 = 9
if @children2 > 9 set @children2 = 9

if @datestamp = @arrivaldate 
begin
  if @adults > 1 
    set @addonadultsfields = 'AdultArrivalAddon' + Convert(VarChar(2), @adults) 
  else
    set @addonadultsfields = 'AdultArrivalAddon'
  if @children1 > 1 
    set @addonchildren1fields = 'Children1ArrivalAddon' + Convert(VarChar(2), @children1) 
  else
    set @addonchildren1fields = 'Children1ArrivalAddon'
  if @children2 > 1 
    set @addonchildren2fields = 'Children2ArrivalAddon' + Convert(VarChar(2), @children2) 
  else
    set @addonchildren2fields = 'Children2ArrivalAddon'
end
else if @datestamp = @departuredate 
begin
  if @adults > 1 
    set @addonadultsfields = 'AdultDepartureAddon' + Convert(VarChar(2), @adults) 
  else
    set @addonadultsfields = 'AdultDepartureAddon'
  if @children1 > 1 
    set @addonchildren1fields = 'Children1DepartureAddon' + Convert(VarChar(2), @children1) 
  else
    set @addonchildren1fields = 'Children1DepartureAddon'
  if @children2 > 1 
    set @addonchildren2fields = 'Children2DepartureAddon' + Convert(VarChar(2), @children2) 
  else
    set @addonchildren2fields = 'Children2DepartureAddon'
end
else
begin
  if @adults > 1 
   set @addonadultsfields = 'AdultStayAddon' + Convert(VarChar(2), @adults) 
  else
    set @addonadultsfields = 'AdultStayAddon'
  if @children1 > 1 
    set @addonchildren1fields = 'Children1StayAddon' + Convert(VarChar(2), @children1) 
  else
    set @addonchildren1fields = 'Children1StayAddon'
  if @children2 > 1 
    set @addonchildren2fields = 'Children2StayAddon' + Convert(VarChar(2), @children2) 
  else
    set @addonchildren2fields = 'Children2StayAddon'
end

set @adultfield     = 'AdultPrice' +  + Convert(VarChar(2), @adults)
set @children1field = 'Children1Price' +  + Convert(VarChar(2), @children1)
set @children2field = 'Children2Price' +  + Convert(VarChar(2), @children2)

if @adults > 0 
  set @allfields = @allfields + @adultfield
else 
  set @allfields = '0'

if @children1 > 0 
  set @allfields = @allfields + ' + ' + @children1field 
if @children2 > 0 
  set @allfields = @allfields + ' + ' + @children2field

if @adults > 0 
  set @allfields = @allfields + ' + ' + @addonadultsfields 
if @children1 > 0
  set @allfields = @allfields + ' + ' + @addonchildren1fields 
if @children2 > 0
  set @allfields = @allfields + ' + ' + @addonchildren2fields

set @sql = 'select ' + @allfields + ' as result from ProductPrice WITH (nolock) where productnumber = ' + convert(varchar(10), @productnumber) + ' and datestamp = ' + @date + ' and Roomtypenumber = ' + convert(varchar(10), @roomtypenumber)
exec sp_sqlexec @sql

