title: Get different scopes for an employee
body: <p>I am retrieving the different roles/scopes of an employee using a dynamic query like below :</p>
  
  <pre><code>  DECLARE @roles NVARCHAR(MAX)=''
          DECLARE @table TABLE
      (
          Scope NVARCHAR(MAX)
      )
  
      SELECT @roles=@roles+ 'Select '+isnull(er.ColumnName,'*')+' from '+er.SchemaName+'.'+er.TableName+' where '+kcu.COLUMN_NAME +'='+er1.ValueId 
  
  
         from [Function].[Role] er 
          left join INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc on 
              tc.TABLE_NAME=er.TableName and tc.TABLE_SCHEMA=er.SchemaName
         left join INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu on kcu.CONSTRAINT_NAME=tc.CONSTRAINT_NAME
          left JOIN Employee_Role er1 ON er.EntityRoleId = er1.RoleId
          left  JOIN Employee e ON er1.EmployeeId = e.EmployeeId
      where e.EmployeeId=54 AND tc.CONSTRAINT_TYPE='PRIMARY KEY' AND er1.ValueId !=0 AND er1.ValueId IS NOT NULL
  
      INSERT INTO @table(Scope)
    EXEC sp_executesql @roles;
  
    SELECT * FROM @table
  </code></pre>
  
  <p>This is my output : </p>
  
  <p><a href="https://i.stack.imgur.com/FyMbo.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/FyMbo.png" alt="enter image description here"></a></p>
  
  <p>When I try to display the <code>EmployeeId</code> with roles (scopes) like below :</p>
  
  <pre><code>DECLARE @roles NVARCHAR(MAX)=''
      DECLARE @table TABLE
  (
      Scope NVARCHAR(MAX)
  )
  
      SELECT  e.EmployeeId,
  
  'Select '+isnull(er.ColumnName,'*')+' from '+er.SchemaName+'.'+er.TableName+' where '+kcu.COLUMN_NAME +'='+er1.ValueId 
  
  
         from [Function].[Role] er 
          left join INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc on 
              tc.TABLE_NAME=er.TableName and tc.TABLE_SCHEMA=er.SchemaName
         left join INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu on kcu.CONSTRAINT_NAME=tc.CONSTRAINT_NAME
          left JOIN Employee_Role er1 ON er.EntityRoleId = er1.RoleId
          left  JOIN Employee e ON er1.EmployeeId = e.EmployeeId
      where e.EmployeeId=54 AND tc.CONSTRAINT_TYPE='PRIMARY KEY' AND er1.ValueId !=0 AND er1.ValueId IS NOT NULL
  
      INSERT INTO @table(Scope)
    EXEC sp_executesql @roles;
  
    SELECT * FROM @table
  </code></pre>
  
  <p>My output is empty.
  Expected output :</p>
  
  <p><a href="https://i.stack.imgur.com/alXtq.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/alXtq.png" alt="enter image description here"></a></p>