USP_BATCH_GETORIGINALNUMBERANDLEVEL

Returns the original batch number and level when generating an exception batch number.

Parameters

Parameter	Parameter Type	Mode
@BATCHID	uniqueidentifier	IN
@ORIGINALBATCHNUMBER	nvarchar(100)	INOUT
@LEVEL	int	INOUT

Definition

Copy



      CREATE procedure dbo.USP_BATCH_GETORIGINALNUMBERANDLEVEL
      (
        @BATCHID uniqueidentifier,
        @ORIGINALBATCHNUMBER nvarchar(100) output,
        @LEVEL int output
      )
      as
        set nocount on;

        if not exists(select BATCH.ID from BATCH where ID = @BATCHID)
          return;

        declare  @ORIGINATINGBATCHID uniqueidentifier;

        set @LEVEL = -1;
        set @ORIGINATINGBATCHID = @BATCHID;

        while @ORIGINATINGBATCHID is not null
        begin

         select 
          @LEVEL = @LEVEL + 1, 
          @ORIGINALBATCHNUMBER = BATCHNUMBER,
          @ORIGINATINGBATCHID = ORIGINATINGBATCHID
         from dbo.BATCH  
         where ID = @ORIGINATINGBATCHID

         --If they hit more than a thousand just stop

         if @LEVEL > 1000
          break;

        end