title: SQL Server Recursive Stored Procedure
body: <p>I'm trying to list all files in a virtual directory and it's sub directories. This could be employees in a company with subordinates, this is not a file system. Maybe recursive store procedure may not be the answer.</p>
  
  <p>Scenario:</p>
  
  <ul>
  <li>Directory Table: <code>DirId, ParentId</code></li>
  <li>Files Table: <code>FileId, DirId</code></li>
  </ul>
  
  <p><code>ParentId</code> is the parent directory and root directory has <code>parentId = NULL</code>... think it's self explanatory.</p>
  
  <p>Now the problem... I want a list files that exist in a directory and its sub directories.</p>
  
  <p>For just one directory I would create a stored procedure:</p>
  
  <pre><code>SELECT * FROM Files Where DirId = ????
  </code></pre>
  
  <p>So how would I create a stored procedure to include sub directories? At the moment I am using C# code and looping through each directory. I prefer to use a stored procedure... unless you prove me wrong.</p>