Query / Explanation
The purpose is to pull all records for a delegation chain and add a new record at the beginning of the approval chain. RowID is used when talking with an external system and is a required part of the returned results.
I'm wondering if there's a better way that I could have constructed this query instead of the approach I took below? Beyond that I'm definitely interested in general performance / formatting suggestions.
/* Define ye values */ declare @role_group as int = 55 declare @position as char(55) = 'Asset Analyst' declare @delegation_text as int = null declare @delegation_amount as int = 0 declare @entity as int = 1410 declare @project_code as int = 10022 declare @delegation_code as varchar(10) = 'NC' select top 1 '1' + cast(@entity as varchar(10)) + @delegation_code as 'RowID' , 1 as 'ApprovalOrder' , @entity as 'EntityCode', CostCentre as 'CostCentre', @delegation_code as 'DelegationCode' , @project_code as 'ProjectCode' , @role_group as 'RoleGroup' , @position as 'Position', @delegation_text as 'DelegationText' , @delegation_amount as 'DelegationAmount' , Active from workflow.delegation_engine where EntityCode = @entity and ProjectCode = @project_code and DelegationCode = @delegation_code union select de.RowID , cast(de.ApprovalOrder as int) + 1 as 'ApprovalOrder' , de.EntityCode , de.CostCentre , de.DelegationCode , de.ProjectCode , de.RoleGroup , de.Position , de.DelegationText , de.DelegationAmount , de.Active from ( select cast(de.ApprovalOrder + 1 as varchar(4)) + cast(de.EntityCode as varchar(8)) + cast(de.DelegationCode as varchar(12)) as 'RowID' , ApprovalOrder , EntityCode , CostCentre , DelegationCode , ProjectCode , RoleGroup , Position , DelegationText , DelegationAmount , Active from workflow.delegation_engine de ) de where de.EntityCode = @entity and de.ProjectCode = @project_code and de.DelegationCode = @delegation_code