USP_DATALIST_GOALVSACTUALDOLLORS

CREATE procedure dbo.USP_DATALIST_GOALVSACTUALDOLLORS(@EVENTID uniqueidentifier)
as
    set nocount on;
  DECLARE @SPONSORSHIPGOAL money
  DECLARE @PARTICIPANTGOAL money
  DECLARE @TEAMGOAL money
  DECLARE @COMPANYGOAL money
  DECLARE @SPONSORSHIPACTUAL money
  DECLARE @PARTICIPANTACTUAL money
  DECLARE @TEAMACTUAL money
  DECLARE @COMPANYACTUAL money
  declare @DetailedRevenue table
 (
    REVENUETYPENAME varchar(50),
    GOAL money,
    ACTUAL money
 )
  select @SPONSORSHIPGOAL=REVENUESPONSOR,@PARTICIPANTGOAL=REVENUEINDIVIDUAL,@TEAMGOAL=REVENUETEAM,@COMPANYGOAL=REVENUECOMPANY from dbo.EVENTGOAL
  where EVENTID = @EVENTID
  DECLARE @SQL nvarchar(255)
  DECLARE @PARAMS nvarchar(255)
  DECLARE @SMARTFIELDID uniqueidentifier = null

  select @SMARTFIELDID =ID from SMARTFIELD where SMARTFIELDCATALOGID = '04edb4f4-eb98-4bff-8ee7-2e578cd448e2'
  if @SMARTFIELDID is not null
  begin
     set @SQL='select @SPONSORSHIPACTUAL = VALUE from dbo.SMARTFIELD' + REPLACE(@SMARTFIELDID,'-','') + ' where ID = @EVENTID'
     set @PARAMS = '@EVENTID uniqueidentifier, @SPONSORSHIPACTUAL money output'
     exec sp_executesql @SQL, @PARAMS, @EVENTID = @EVENTID, @SPONSORSHIPACTUAL = @SPONSORSHIPACTUAL output
  end

  set @SMARTFIELDID = null
  select @SMARTFIELDID =ID from SMARTFIELD where SMARTFIELDCATALOGID = 'a65975a1-0cbb-4028-9e66-b108ce12eb08'
  if @SMARTFIELDID is not null
  begin
     set @SQL='select @PARTICIPANTACTUAL = VALUE from dbo.SMARTFIELD' + REPLACE(@SMARTFIELDID,'-','') + ' where ID = @EVENTID'
     set @PARAMS = '@EVENTID uniqueidentifier, @PARTICIPANTACTUAL money output'
     exec sp_executesql @SQL, @PARAMS, @EVENTID = @EVENTID, @PARTICIPANTACTUAL = @PARTICIPANTACTUAL output
  end

  set @SMARTFIELDID = null
  select @SMARTFIELDID =ID from SMARTFIELD where SMARTFIELDCATALOGID = '8801e0fb-ef88-4815-acc3-f33019f22925'
  if @SMARTFIELDID is not null
  begin
     set @SQL='select @TEAMACTUAL = VALUE from dbo.SMARTFIELD' + REPLACE(@SMARTFIELDID,'-','') + ' where ID = @EVENTID'
     set @PARAMS = '@EVENTID uniqueidentifier, @TEAMACTUAL money output'
     exec sp_executesql @SQL, @PARAMS, @EVENTID = @EVENTID, @TEAMACTUAL = @TEAMACTUAL output
  end

  set @SMARTFIELDID = null
  select @SMARTFIELDID =ID from SMARTFIELD where SMARTFIELDCATALOGID = 'f0f13156-3b33-488a-bc66-f05b612113c4'
  if @SMARTFIELDID is not null
  begin
     set @SQL='select @COMPANYACTUAL = VALUE from dbo.SMARTFIELD' + REPLACE(@SMARTFIELDID,'-','') + ' where ID = @EVENTID'
     set @PARAMS = '@EVENTID uniqueidentifier, @COMPANYACTUAL money output'
     exec sp_executesql @SQL, @PARAMS, @EVENTID = @EVENTID, @COMPANYACTUAL = @COMPANYACTUAL output
  end

    insert into  @DetailedRevenue(REVENUETYPENAME, GOAL, ACTUAL) VALUES('Corporate sponsorship income',@SPONSORSHIPGOAL,@SPONSORSHIPACTUAL)
  insert into  @DetailedRevenue(REVENUETYPENAME, GOAL, ACTUAL) VALUES('Participant income',@PARTICIPANTGOAL,@PARTICIPANTACTUAL)
  insert into  @DetailedRevenue(REVENUETYPENAME, GOAL, ACTUAL) VALUES('Team income',@TEAMGOAL,@TEAMACTUAL)
  insert into  @DetailedRevenue(REVENUETYPENAME, GOAL, ACTUAL) VALUES('Company fundraising',@COMPANYGOAL,@COMPANYACTUAL) 
  select REVENUETYPENAME,GOAL, IsNull(ACTUAL, 0) from @DetailedRevenue