title: pass parameter to CTE
body: <p>I have a query with 3 CTE's.  I am curious how to pass a parameter into them.  If I declare at the top of the query something to the effect of </p>
  
  <p>declare @prioryear varchar(5)
  set @prioryear = '2014'</p>
  
  <p>declare @currentyear varchar(5)
  set @prioryear = '2015'</p>
  
  <p>Then how can I pass those variables into the CTE's?  I keep trying to put paren's after the CTE names but nothing seems to want to pass.</p>
  
  <p>Thanks,
  Joe</p>
  
  <p>`</p>
  
  <pre><code>with paidPrior as (
  SELECT DISTINCT des_heapplicationSet.des_PeopleId
  FROM         des_heapplicationSet INNER JOIN
                        des_zyearSet ON des_heapplicationSet.des_YearId = des_zyearSet.des_zyearId INNER JOIN
                        des_zapplicationstatusSet ON des_heapplicationSet.des_ApplicationStatusId = des_zapplicationstatusSet.des_zapplicationstatusId
  
  WHERE     (des_zyearSet.des_YearText = '2014') AND (des_zapplicationstatusSet.des_applicationstatustext = 'paid') and des_IsMaster =0
  ),
  
  allCurrent as (
  SELECT DISTINCT des_heapplicationSet.des_PeopleId
  FROM         des_heapplicationSet INNER JOIN
                        des_zyearSet ON des_heapplicationSet.des_YearId = des_zyearSet.des_zyearId
  WHERE     (des_zyearSet.des_YearText = '2015') and des_IsMaster =0
  ),
  
  peopleSet as (
  select distinct des_PeopleId, MAX(des_ApplicationNumber) as appNum
  from des_heapplicationSet
  where des_PeopleId in (select * from paidPrior) and des_PeopleId not in (select * from allCurrent)
  group by des_PeopleId
  )
  
  SELECT     des_zterritorySet.des_TerritoryText, des_hepeopleSet.des_PeopleNumber, des_hepeoplenameSet.des_FirstName, des_hepeoplenameSet.des_LastName, des_headdressmasterSet.des_AddressLine, 
                        des_headdressmasterSet.des_FinalCity, des_headdressmasterSet.des_Zip5, des_headdressmasterSet.des_CountyName, 'WI' as stateOfWi,  des_hepeoplephoneSet.des_PhoneNumber, 
                        des_heapplicationSet.des_EmailAddress
  FROM         des_heapplicationSet INNER JOIN
                        des_hepeopleSet ON des_heapplicationSet.des_PeopleId = des_hepeopleSet.des_hepeopleId INNER JOIN
                        des_headdressmasterSet ON des_heapplicationSet.des_MailingAddressId = des_headdressmasterSet.des_headdressmasterId INNER JOIN
                        des_zterritorySet ON des_heapplicationSet.des_TerritoryId = des_zterritorySet.des_zterritoryId INNER JOIN
                        des_hepeoplenameSet ON des_heapplicationSet.des_PeopleNameId = des_hepeoplenameSet.des_hepeoplenameId LEFT OUTER JOIN
                        des_hepeoplephoneSet ON des_heapplicationSet.des_PeoplePrimaryPhoneId = des_hepeoplephoneSet.des_hepeoplephoneId
  where des_ApplicationNumber in (select appnum from peopleSet) and des_TerritoryText = 'Adams'
  order by des_TerritoryText
  `
  </code></pre>