title: Transact SQL - How do I include an IF..THEN or CASE...WHEN within a Function?
body: <p>I am relatively new to SQL but have good programming experience behind me. I have a piece of code that was given to me to create a 'Function' which returns values into a table. However, I have a problem with NULLS when the code is trying to compare 2 sets of data to give a percentage difference. Where one set of data is all NULLS then the Join cannot happen on the INSERT so it returns nothing although really there is a difference of 100%. I want to add in an IF statement so that if the count of one of the sets of data is zero (they were all nulls) then it defaults a value and if there are 2 genuine sets of data then it joins the 2 and calculates? Hope this explains it enough. Below is most of the code - DS1VALS or DS2VALS could return nothing if there is NO data. I have tried pretty much everything around this area to add an IF EXISTS or ISNULL but nothing works. I did get a message that suggested that a FUNCTION cannot return the answer to the IF etc??? 
  Thanks.</p>
  
  <pre><code>-- get the PeriodicDataElementInstanceID for the datasources that we're using
  select @DataSource1_PDEIID = PeriodicDataElementInstanceID
  from PeriodicDataElementInstance
  where Name = @DS1
  
  select @DataSource2_PDEIID = PeriodicDataElementInstanceID
  from PeriodicDataElementInstance
  where Name = @DS2
  
  -- get the start and end date of the periods in the past that we are checking.
  -- these are based on the Calendar, calendar period type, and system start date set in the Universal
  -- system options.
  select @fromDate = StartDate
  from dbo.GetPeriodsDateByPeriodsOffset(@X)
  
  select @toDate = EndDate
  from dbo.GetPeriodsDateByPeriodsOffset(@Y)
  
  select @fromDate2 = StartDate 
  from dbo.GetPeriodsDateByPeriodsOffset(@XX) 
  
  select @todate2 = EndDate 
  from dbo.GetPeriodsDateByPeriodsOffset(@YY) 
  
  ;with
  DS1VALS (EI, Value) as
  (select pds.EndItemID, sum(pd.value)
  from PeriodicDataStream pds 
  join PeriodicData pd on pd.PeriodicDataStreamID = pds.PeriodicDataStreamID
  where pds.PeriodicDataElementInstanceID = @DataSource1_PDEIID
  and pd.PeriodDate between @fromDate and @todate
  group by pds.EndItemID),
  DS2VALS (EI, Value) as
  (select pds.EndItemID, sum(pd.value)
  from PeriodicDataStream pds 
  join PeriodicData pd on pd.PeriodicDataStreamID = pds.PeriodicDataStreamID
  where pds.PeriodicDataElementInstanceID = @DataSource2_PDEIID
  and pd.PeriodDate between @fromDate2 and @todate2 
  group by pds.EndItemID)
  
  --select * from DS1VALS ds1
  --join DS2VALS ds2 on ds1.EI = ds2.EI
  
  Insert into @Results
  select ds1.EI,  
      case 
      when (convert(numeric(19,9),ds1.Value) &lt;&gt; 0)
      then (convert(numeric(19,9),ds2.Value) / convert(numeric(19,9),ds1.Value) - 1) * 100
      else 9999999999
      end
  from DS1VALS ds1
  join DS2VALS ds2 on ds1.EI = ds2.EI
  where convert(numeric(19,9),ds1.Value) &lt;&gt; convert(numeric(19,9),ds2.Value);
  
  Return
  End
  </code></pre>