title: SQL Function not returning all rows
body: <p>What I need: All crossings, and the AppData record for that year (if there is one, else null). Should always return 38,244 records.</p>
  
  <p>What's happening: If I run it for 2012, I get all rows; if I run it for 2013, I only get 19248 rows. </p>
  
  <p>On 1st application, a record is INSERTED into the AppData table. It IS possible that there will be crossings that do NOT have a record for the year.  On 2nd or 3rd application, it's an Update record; there will ALWAYS be a record associated to the crossing. </p>
  
  <p>I've been at this for days. Any help greatly appreciated!!</p>
  
  <p>Table Data
  Crossings Table - 
  CRID, int</p>
  
  <p>AppData table
  AppID, AppDate, AppYear.</p>
  
  <pre><code>    ALTER FUNCTION [dbo].[fnGetAppData2]
      (
  -- Add the parameters for the function here
      @app as varchar(4),
       @year as varchar(4)
       )
      RETURNS 
      @SCLApps TABLE 
  
  -- Add the column definitions for the TABLE variable here
  (AppCRID int,
  AppDate date,
  AppNbr int, 
  AppTruck varchar(10), 
  Spray bit,
  Cut bit,
  Inspect bit,
  Invoice date,
  AppYear int)
  
      AS
      BEGIN
      --for 1st app
  -- Fill the table variable with the rows for your result set
  
  If @app = 1
  
  INSERT @SCLApps (AppCRID,AppDate,AppTruck,Cut,Inspect,Spray,Invoice,AppNbr)
  SELECT Crossings.CRID,
  
  --application date
  (CASE WHEN @app = 1 THEN
  App1Date 
  WHEN @app = 2 THEN
  App2Date
  WHEN @app = 3 THEN
  App3Date
  ELSE
  Null
  END), 
  
  --application truck
  (CASE WHEN @app = 1 THEN
  App1Truck
  WHEN @app = 2 THEN
  App2Truck
  WHEN @app = 3 THEN
  App3Truck
  ELSE
  Null
  END), 
  
  --app cut
  (CASE WHEN @app = 1 THEN
  App1Cut
  WHEN @app = 2 THEN
  App2Cut
  WHEN @app = 3 THEN
  App3Cut
  ELSE
  Null
  END), 
  
  --app inspect
  (CASE WHEN @app = 1 THEN
  App1Inspect
  WHEN @app = 2 THEN
  App2Inspect
  WHEN @app = 3 THEN
  App3Inspect
  ELSE
  Null
  END), 
  
  --app spray  
  (CASE WHEN @app = 1 THEN
      App1Spray
  WHEN @app = 2 THEN
      App2Spray
  WHEN @app = 3 THEN
      App3Spray
      ELSE
  Null
  END),
  
  --invoice
  (CASE WHEN @app = 1 THEN
      App1InvDate
  WHEN @app = 2 THEN
      App2InvDate
  WHEN @app = 3 THEN
      App3InvDate
      ELSE
  Null
  END),
  
  --AppNbr
  (CASE WHEN @app = 1 THEN
  1
  WHEN @app = 2 THEN
  2
  WHEN @app = 3 THEN
  3
  ELSE
  Null
  END)
  
  FROM AppData full OUTER JOIN Crossings ON AppData.CRID = Crossings.CRID
  WHERE (AppYear = @year or AppYear is null)
  
  ELSE
  
  
  
  
  --for 2nd and 3rd app--------------------------------------
  
  INSERT @SCLApps (AppCRID,AppDate,AppTruck,Cut,Inspect,Spray,Invoice, AppNbr)
  SELECT Crossings.CRID,
  
  --application date
  (CASE WHEN @app = 1 THEN
  App1Date
  WHEN @app = 2 THEN
  App2Date
  WHEN @app = 3 THEN
  App3Date
  ELSE
  ''
  END), 
  
  --application truck
  (CASE WHEN @app = 1 THEN
  App1Truck
  WHEN @app = 2 THEN
  App2Truck
  WHEN @app = 3 THEN
  App3Truck
  ELSE
  ''
  END), 
  
  --app cut
  (CASE WHEN @app = 1 THEN
  App1Cut
  WHEN @app = 2 THEN
  App2Cut
  WHEN @app = 3 THEN
  App3Cut
  ELSE
  ''
  END), 
  
  --app inspect
  (CASE WHEN @app = 1 THEN
  App1Inspect
  WHEN @app = 2 THEN
  App2Inspect
  WHEN @app = 3 THEN
  App3Inspect
  ELSE
  ''
  END), 
  
  --app spray  
  (CASE WHEN @app = 1 THEN
      App1Spray
  WHEN @app = 2 THEN
      App2Spray
  WHEN @app = 3 THEN
      App3Spray
      ELSE
  ''
  END),
  
  --invoice
  (CASE WHEN @app = 1 THEN
      App1InvDate
  WHEN @app = 2 THEN
      App2InvDate
  WHEN @app = 3 THEN
      App3InvDate
      ELSE
  ''
  END),
  
  --AppNbr
  (CASE WHEN @app = 1 THEN
  1
  WHEN @app = 2 THEN
  2
  WHEN @app = 3 THEN
  3
  ELSE
  ''
  END)
  
  FROM AppData full OUTER JOIN Crossings ON AppData.CRID = Crossings.CRID
      WHERE AppYear = @year
  
  RETURN 
      END
  </code></pre>