I have the following query, for each employee I get the list of his different roles :
SELECT e.EmployeeId,r.EntityRoleId, r.Label,er.ValueId FROM Employee e LEFT JOIN Employee_Role er ON e.EmployeeId = er.EmployeeId LEFT JOIN [Function].[Role] r ON er.RoleId = r.EntityRoleId WHERE e.EmployeeId=54
I want to get the scope for each label (role) of any employee (here the EmployeeId=54) :
The logic behind ValueId:
- NULL means that we don’t care about the scope because your Role is
Global (not scoped on a specific Entity)
- 2 in the dbo.Employee_Department table matches “DNA” product.
- 1 in the dbo.Employee_Department table matches “Software Factory”
Which means that : a role can be linked to any entity in the DB (cf. [SchemaName], [TableName], [ColumnName] columns in [Function].[Role])
In Employee_Role, ValueId matches the PK of the table specified in [Function].[Role].
So to get the label of the role RoleId = 250 for EmployeeId = 5255, get the ValueId=2 and look into the table Product with the Schema HoA and get the record having the PrimaryKey(can change from a table to another) =ValueId = 2, the corresponding Label column matches the Role label as below :
SELECT e.EmployeeId,r.EntityRoleId, r.Label,er.ValueId FROM Employee e LEFT JOIN Employee_Role er ON e.EmployeeId = er.EmployeeId LEFT JOIN [Function].[Role] r ON er.RoleId = r.EntityRoleId INNER JOIN sys.indexes i on object_name(i.object_id) = r.TableName INNER JOIN sys.tables t on i.object_id = t.object_id and SCHEMA_NAME(t.schema_id) = r.SchemaName INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id INNER JOIN sys.columns c ON ic.object_id = c.object_id AND c.column_id = ic.column_id WHERE i.is_primary_key = 1 ORDER BY e.EmployeeId
I can get the column corresponding to primary key (ValueId)=2, I want to get the content of the column Label existing in the corresponding table which is “DNA” in the screenshot.
My question is how to get the label of each role in this situation?
This can be solved with dynamic SQL. Alternatively, you can convert your resulting row into XML, where each column will be turned into an attribute. This allows you to produce a name/pair values from your row, and filter based upon the column name you were interested in.
Here is a POC, where the contents of the CTE Test would be your row(s) and @Field is the field your are trying to extract. I’ve added a RowNumber field in case multiple rows were returned. Note: I would expect dynamic SQL to be more performant.
DECLARE @Field NVARCHAR(128) = 'Label'; WITH Test AS ( SELECT 1 a, 2 b, 'greg' Label UNION SELECT 2 a, 4 b, 'jan' Label ) SELECT col.value('.././@RowNumber', 'int') AS RowNumber -- Not needed for single row results , col.value('local-name(.)', 'nvarchar(max)') AS [name] , col.value('.', 'nvarchar(max)') AS [value] FROM ( SELECT CAST( ( SELECT * , ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS RowNumber -- Not needed for single row results FROM Test FOR XML AUTO, ROOT ) -- Wrap to treat as a single result AS XML) -- convert to XML to wok with it ) D(X) CROSS APPLY X.nodes('//@*') N(col) -- Get all Attributes WHERE col.value('local-name(.)', 'nvarchar(max)') = @field -- limit to the field we care about