# 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

• I like the formatting style (i.e. the indentation and separation of each condition onto its own line), but (without trying to execute it) I don't see the purpose of the subquery. Jan 12 '12 at 1:00
• @Dr.Wily'sApprentice Ah, a bi-product of cutting it down for posting. The full query orders by RowID and the only way to do that (to the best of my knowledge) is by using the sub-query Jan 12 '12 at 1:04
• You can do order by 1, 2, 3 to say "order by the first column, then the second, then the third". You should be able to use that to order by your calculated column so that you don't have to use the subquery. Also, I would suggest considering adding an actual "RowID" computed column to the table schema. Jan 12 '12 at 1:06
• @Dr.Wily'sApprentice That's a very good point actually - hadn't considered that, had the rigid idea in my head that I had to sort by RowID. Thank-you. Jan 12 '12 at 1:27

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

• I agree on both points and have raised this with the dba team previously. Unfortunately this hasn't happened yet so my plan is to implement my code using this query, then refer to them for performance issues (knowing that their first point of call will be indexes, only made possible by making this a computed column). Office politics - blargh! Jan 12 '12 at 1:26
• @Michael - I understand your pain! Well, a potential alternative is to create a view that includes the RowID column. I believe that an index can be applied to a view, so that might be another way to circumvent performance issues without modifying the actual table structure. Jan 12 '12 at 1:30