UFN_BATCH_ROOTBATCHID

CREATE function dbo.[UFN_BATCH_ROOTBATCHID]
(
    @BATCHID uniqueidentifier
)
returns uniqueidentifier
as begin
    declare @ROOTBATCHID uniqueidentifier = null;
    declare @PARENTID uniqueidentifier;

    select @PARENTID = [ORIGINATINGBATCHID] from dbo.[BATCH] where [ID] = @BATCHID;

    while @PARENTID is not null begin
        set @ROOTBATCHID = @PARENTID;
        select @PARENTID = [ORIGINATINGBATCHID] from dbo.[BATCH] where [ID] = @PARENTID;
    end

    return @ROOTBATCHID;
end