title: Recursive CTE in SQL Server to count blocking sessions
body: <p>I'm trying to make CTE to count blocking sessions on server. Currently I made a function to do so(returns blocking session amount). But still could not write this query with CTE.</p>
  <pre><code>CREATE FUNCTION dbo.SESSIONS(@SESSION int)
  RETURNS INT
  AS 
  BEGIN
      DECLARE  @returnValue int =0
      SET @returnValue = @returnValue + (         
              SELECT isNULL((
              SELECT (SELECT COUNT(session_id) FROM sys.dm_exec_requests R2 WHERE blocking_session_id =R1.blocking_session_id) AS [BLOCKING]
              FROM sys.dm_exec_requests r1 
              WHERE blocking_session_id = R3.session_id
              GROUP BY blocking_session_id), 0)
              FROM sys.dm_exec_sessions R3 where session_id=@SESSION)
  
      DECLARE @s int = (SELECT MAX(session_id) FROM  sys.dm_exec_requests
                        WHERE blocking_session_id = @SESSION)
  
      IF @s IS NOT NULL
      BEGIN
          SET @returnValue = @returnValue + dbo.SESSIONS(@s)
      END
  
      RETURN @returnValue
  END
  </code></pre>
  <p>Can someone please share your ideas?</p>
  <p>Many thanks.</p>