UFN_BATCH_GETEXCEPTIONCHAINANCESTORS

            create function dbo.UFN_BATCH_GETEXCEPTIONCHAINANCESTORS
            (
                @BATCHID uniqueidentifier
            )
            returns @ANCESTORBATCHES table
            (
                    ID uniqueidentifier,
                    BATCHNUMBER nvarchar(100),
                    ORIGINATINGBATCHID uniqueidentifier,
                    [LEVEL] integer
            )
            as
            begin
                declare @BATCHNUMBER nvarchar(100);
                declare @ORIGINATINGBATCHID uniqueidentifier;    
                declare @LEVEL integer = 0;

                if exists(select ID from dbo.BATCH where ID = @BATCHID)
                while @BATCHID is not null
                begin
                        select @LEVEL = @LEVEL - 1

                        select @BATCHID = [PARENTBATCH].ID, @BATCHNUMBER = [PARENTBATCH].BATCHNUMBER, @ORIGINATINGBATCHID = [PARENTBATCH].ORIGINATINGBATCHID
                        from 
                            dbo.BATCH [CHILDBATCH]
                            left join dbo.BATCH [PARENTBATCH] on [CHILDBATCH].ORIGINATINGBATCHID = [PARENTBATCH].ID
                        where [CHILDBATCH].ID = @BATCHID

                        if @BATCHID is not null
                            insert into @ANCESTORBATCHES(ID,BATCHNUMBER,ORIGINATINGBATCHID,[LEVEL])
                            values(@BATCHID,@BATCHNUMBER,@ORIGINATINGBATCHID,@LEVEL)

                        --If they hit more than a thousand just stop

                        if @LEVEL > 1000
                            break;
                end

                return
            end