title: T_SQL Function with 'with' clause
body: <p>I am wondering why I cannot create the following T-SQL function:</p>
  
  <pre><code>CREATE FUNCTION DaysIncarceratedInYear
  --Requires a Patient ID and a year
  -- will return the number of days the patient was incarcerated for the year
  
  --incarcerated is stored in a flowsheet
  -- the FlowdataID are:
  --                Incarceration Start Date:  'ZZZZZ00071
  --                Incarceration End Date  :  'ZZZZZ00072'
  
  
  (
  @PID varchar(10),
  @YEAR int
  )
  
  RETURNS int
  
  --DECLARE @PID varchar(10)
  --DECLARE @YEAR int
  --set @PID = 'ZZZZZ000L6'
  --set @YEAR =2012
  
  AS
  BEGIN
  declare @R int
  
  
  SELECT @R = Numdays from (
                  ;with startdates as
                  (
                  SELECT
                  dbo.View_PatientFlowValue.FlowValue_Value as val,
                  ROW_NUMBER() OVER(ORDER BY dbo.View_PatientFlowValue.FlowValue_Value) AS RowNumber
                  FROM dbo.View_PatientFlowValue
                  WHERE dbo.View_PatientFlowValue.FlowData_ID = 'ZZZZZ00071' 
                  AND dbo.View_PatientFlowValue.FlowValue_RecordState&lt;&gt;1
                  AND Patient_ID = @PID
  
                  )
                  ,enddates as
                  (
                  SELECT
                  dbo.View_PatientFlowValue.FlowValue_Value val,
                  ROW_NUMBER() OVER(ORDER BY dbo.View_PatientFlowValue.FlowValue_Value) AS RowNumber
                  FROM dbo.View_PatientFlowValue
                  WHERE dbo.View_PatientFlowValue.FlowData_ID = 'ZZZZZ00072' 
                  AND dbo.View_PatientFlowValue.FlowValue_RecordState&lt;&gt;1
                  AND Person_ID = @PID
  
                  )
  
                  Select sum(DATEDIFF(d,CalcStart, CalcEnd)) as NumDays
  
                  from (
                          select 
                              case 
                                  when DATEDIFF(d, cast(str(@YEAR*10000+1*100+1) as date), s.val) &lt; 1 then '1/1/' + str(@YEAR)
                                  else s.val
                              end As CalcStart,
                              ISNULL(e.Val, cast(str((@YEAR+1)*10000+1*100+1) as date)) as CalcEnd,
                          s.val as realstart, e.val as realend
                          FROM StartDates s
                          LEFT OUTER JOIN EndDates e ON s.RowNumber = e.RowNumber
                      ) accountforyear 
  
          ) 
  
  return @R
  END
  GO
  </code></pre>
  
  <p>It states there is incorrect syntax near ";" , but if I take the ";" out, it tells me there is incorrect syntax near the keyword "with".  What is the proper syntax here?  </p>
  
  <p>This query works fine standalone.</p>