UFN_BATCHTEMPLATE_GETORIGINATINGTEMPLATENAME

                create function dbo.UFN_BATCHTEMPLATE_GETORIGINATINGTEMPLATENAME(
                    @BATCHTEMPLATEID uniqueidentifier
                ) 
                returns nvarchar(60) with execute as caller
                as
                begin

                declare @BATCHTEMPLATENAME as nvarchar(60);

                with BATCHTEMPLATECHAIN (ID, LINKNUMBER, BATCHTEMPLATENAME, PARENTID) as (
                    --this CTE builds the chain...

                    select
                        BATCHTEMPLATE.ID,
                        0 as LINKNUMBER,
                        BATCHTEMPLATE.NAME as BATCHTEMPLATENAME,
                        BATCHTEMPLATE.ORIGINATINGTEMPLATEID as PARENTID
                    from dbo.BATCHTEMPLATE
                        where 
                        BATCHTEMPLATE.ID = @BATCHTEMPLATEID
                    union all
                    select
                        BATCHTEMPLATECHAIN.ID,
                        BATCHTEMPLATECHAIN.LINKNUMBER + 1 as LINKNUMBER,
                        BATCHTEMPLATE.NAME as BATCHTEMPLATENAME,
                        BATCHTEMPLATE.ORIGINATINGTEMPLATEID as PARENTID
                    from BATCHTEMPLATECHAIN
                    inner join dbo.BATCHTEMPLATE on BATCHTEMPLATE.ID = BATCHTEMPLATECHAIN.PARENTID
                ), BATCHTEMPLATECHAINMAXLINK (ID, MAXLINKNUMBER) as (
                -- ...this CTE finds the last link in the chain...

                    select
                        BATCHTEMPLATECHAIN.ID,
                        max(BATCHTEMPLATECHAIN.LINKNUMBER) as MAXLINKNUMBER
                    from BATCHTEMPLATECHAIN
                    group by BATCHTEMPLATECHAIN.ID
                ), ULTIMATEBATCHTEMPLATE (ID, BATCHTEMPLATENAME) as (
                -- ...and this CTE reveals the ID and name of the last link in the chain

                    select
                        BATCHTEMPLATECHAIN.ID,
                        BATCHTEMPLATECHAIN.BATCHTEMPLATENAME
                    from BATCHTEMPLATECHAIN
                    inner join BATCHTEMPLATECHAINMAXLINK on BATCHTEMPLATECHAINMAXLINK.ID = BATCHTEMPLATECHAIN.ID and BATCHTEMPLATECHAINMAXLINK.MAXLINKNUMBER = BATCHTEMPLATECHAIN.LINKNUMBER
                )
                select
                    @BATCHTEMPLATENAME = ULTIMATEBATCHTEMPLATE.BATCHTEMPLATENAME
                from ULTIMATEBATCHTEMPLATE;

                return @BATCHTEMPLATENAME;

                end