Adding a new record to the beginning of the approval chain from the delegation chain Table Structure


  ApprovalOrder int 
  
  EntityCode    varchar 
  
  CostCentre varchar
  
  DelegationCode    varchar 
  
  ProjectCode   varchar
  
  RoleGroup varchar
  
  Position  varchar
  
  DelegationText    varchar
  
  DelegationAmount  money
  
  Active    bit


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
My suggestion is to create a "RowID" computed column on the workflow.delegation_engine table.

ALTER TABLE workflow.delegation_engine ADD [RowID] AS
    cast(de.ApprovalOrder + 1 as varchar(4))
    + cast(de.EntityCode as varchar(8))
    + cast(de.DelegationCode as varchar(12))


Your comment indicates that you might be ordering the query on this column.  In that case, you might also want to apply an index on this column.  I haven't tried this out, so I don't know for sure whether an index on this column is allowed, but I think it does satisfy all of the requirements.

Also, you might want/need to pad your values with leading zeros in order to ensure that your calculated RowID is unique.  In that case, you might want to try the following:

    right('0000' + cast(de.ApprovalOrder + 1 as varchar(4)), 4)
    + right('00000000' + cast(de.EntityCode as varchar(8), 8)
    + right('000000000000' + cast(de.DelegationCode as varchar(12), 12)


This would provide uniqueness and would preserve ordering (i.e. ApprovalOrder 10 comes after ApprovalOrder 2).  If you don't care about ordering, then you might not need the leading zeros; you could just insert delimiters to ensure uniqueness.

    cast(de.ApprovalOrder + 1 as varchar(4))
    + '|' + cast(de.EntityCode as varchar(8))
    + '|' + cast(de.DelegationCode as varchar(12))


This would protect against a scenario where one row has ApprovalOrder: 1, EntityCode: 'ABC', DelegationCode: 'DEF' and another row has ApprovalOrder: 1, EntityCode: 'AB', DelegationCode: 'CDEF'.  In that case, the RowID for both rows would be '2ABCDEF'.  Adding the delimiters ensures that the first row would have RowID: '2|ABC|DEF' and the second row would have RowID: '2|AB|CDEF'.


Alternatively, if you can't or don't want to put a "RowID" computed column on the table, then you should be able to just structure the query as shown below.  Note that I've included an "order by" clause that was left out of the original query.  Comments indicate that the reason for the subquery in the original query was to order by the "RowID" column.  The order by 1 clause in the query below accomplishes the same result.

select
    cast(de.ApprovalOrder + 1 as varchar(4))
        + cast(de.EntityCode as varchar(8))
        + cast(de.DelegationCode as varchar(12)) as '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
    workflow.delegation_engine de
where       
    de.EntityCode = @entity
    and de.ProjectCode = @project_code
    and de.DelegationCode = @delegation_code
order by
    1