UFN_BATCH_GETEXCEPTIONCHAINDESCENDANTS

            create function dbo.UFN_BATCH_GETEXCEPTIONCHAINDESCENDANTS
            (
                @BATCHID uniqueidentifier
            )
            returns @DESCENDANTBATCHES 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
                        set @LEVEL = @LEVEL + 1;

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

                        if @BATCHID is not null
                            insert into @DESCENDANTBATCHES(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