title: Combining queries in a SQL Server stored procedure
body: <p>I want to combine privileges from the user and their roles in a stored procedure. To do so currently I am running many queries. The first gets the roles the user has. Then a query to get the privileges for each role. Then a query to get the privileges for the user. Then I combine them all from the code. I would like to be able to do all that from a stored procedure. I have not ever worked with stored procedures so I do not know if it is even possible.</p>
  
  <p><a href="https://i.stack.imgur.com/tJznl.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/tJznl.png" alt="The database structure"></a></p>
  
  <p>Hopefully the diagram will help you understand what I am trying to do.</p>
  
  <p>I started to write one but it is not near finished or functional.</p>
  
  <pre><code>CREATE PROCEDURE get_privileges
      @userId INT
  AS
      SET NOCOUNT ON;
  
      SELECT 
          role_id 
      FROM 
          user_role 
      INNER JOIN 
          role ON user_role.role_id = role.id 
      WHERE 
          user_id = @userId;
  
      -- For each role
      SELECT 
          privilege_id as id, name 
      FROM 
          role_privilege 
      INNER JOIN 
          privilege ON role_privilege.privilege_id = privilege.id 
      WHERE 
          role_id = ${roleId}
  
      SELECT 
          privilege_id as id, name 
      FROM 
          user_privilege 
      INNER JOIN 
          privilege ON user_privilege.privilege_id = privilege.id 
      WHERE 
          user_id = @userId;
  </code></pre>
  
  <p>What do I need to do to finish the procedure?</p>
  
  <ol>
  <li><p>Looping through the results of the first select. No clue how to do this.</p></li>
  <li><p>Combining the results. Found that this could be done by making a temp table and then pushing the result sets from each query to it. Is there a better way?</p></li>
  </ol>
  
  <hr>
  
  <p><strong>Sample</strong>:</p>
  
  <p>user table</p>
  
  <pre><code>+----+----------+
  | id | username |
  +----+----------+
  |  1 | caleb    |
  +----+----------+
  </code></pre>
  
  <p>role table</p>
  
  <pre><code>+------+----------+
  | role |   name   |
  +------+----------+
  |    1 | admin    |
  |    2 | standard |
  +------+----------+
  </code></pre>
  
  <p>privilege table</p>
  
  <pre><code>+-----------+---------------+
  | privilege |     name      |
  +-----------+---------------+
  |         1 | CREATE_USER   |
  |         2 | DELETE_USER   |
  |         3 | ADMIN_VIEW    |
  |         4 | STANDARD_VIEW |
  |         5 | NOTHING       |
  +-----------+---------------+
  </code></pre>
  
  <p>user_privilege table</p>
  
  <pre><code>+---------+--------------+
  | user_id | privilege_id |
  +---------+--------------+
  |       1 |            1 |
  +---------+--------------+
  </code></pre>
  
  <p>user_role table</p>
  
  <pre><code>+---------+---------+
  | user_id | role_id |
  +---------+---------+
  |       1 |       1 |
  +---------+---------+
  </code></pre>
  
  <p>role_privilege table</p>
  
  <pre><code>+---------+--------------+
  | role_id | privilege_id |
  +---------+--------------+
  |       1 |            2 |
  |       1 |            3 |
  |       2 |            4 |
  +---------+--------------+
  </code></pre>
  
  <p>Expected result for <code>user_id = 1</code></p>
  
  <pre><code>+--------------+-------------+
  | privilege_id |    name     |
  +--------------+-------------+
  |            1 | CREATE_USER |
  |            2 | DELETE_USER |
  |            3 | ADMIN_VIEW  |
  +--------------+-------------+
  </code></pre>