title: Incorrect syntax near the keyword 'with'.
body: <p>Hello I'm trying to figure out why switching my compatability mode from 80 to 100 in MSSQL broke my function below?  </p>
  
  <pre><code>    Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (X64)   Apr 22 2011 19:23:43
  Copyright (c) Microsoft Corporation  Express Edition with Advanced Services (64-bit) on
  Windows NT 6.1 &lt;X64&gt; (Build 7601: Service Pack 1)
  </code></pre>
  
  <p>Here is my  function:</p>
  
  <pre><code>GO
  ALTER FUNCTION [dbo].[GetRoot] 
  (
      @Param1 int 
  )
  RETURNS varchar(50)
  AS
  BEGIN
  DECLARE @ReturnValue varchar(50)
  with results as
      (
          select parentouid,net_ouid from net_ou where net_ouid=@Param1
          union all
          select t2.parentouid,t2.net_ouid from net_ou t2 
          inner join results t1 on t1.parentouid = t2.net_ouid where t2.parentouid &lt;&gt; t1.net_ouid
      )   
      select @ReturnValue = net_ou.displayname 
      from  NET_OU RIGHT OUTER JOIN
      results ON net_ou.net_ouid = results.ParentouID where results.parentouid=results.net_ouid
  
      RETURN @ReturnValue
  
  END
  </code></pre>