While Loop does not complete its execution in the stored procedure in SQL Server

Question

Hi All,
I have my stored procedure code as below. When I execute the SP it keeps on running in infinite loop. I have tried many options, like using BEGIN after WHILE, BREAK statement but still no luck. Can someone please advice where I am doing wrong.
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON  
GO  
  
  
--EXEC [dbo].[P_MVPBS_AR_INTERFACE_SRI] 'P_MVPBS_AR_INTERFACE', '01-MAR-2022', 'FALSE'  
ALTER PROCEDURE [dbo].[P_MVPBS_AR_INTERFACE_SRI]    
   @PAPPLICATIONNAME varchar(max) = 'P_MVPBS_AR_INTERFACE',  
   @PBILLPERIOD datetime2(0) = NULL,  
   @PISTESTRUN varchar(max) = 'TRUE'  
AS   
   BEGIN  
  
      /* $Id: P_MVPBS_AR_INTERFACE.sql,v 1.13, 2012-08-16 11:25:08Z, Mark Sims$*/  
      DECLARE  
         @EXUSEREXIT$exception nvarchar(1000)  
  
      BEGIN TRY  
  
         SET @EXUSEREXIT$exception = N'ORA-01013%'  
  
         DECLARE  
            /*  
            *   SSMA warning messages:  
            *   O2SS0356: Conversion from NUMBER datatype can cause data loss.  
            */  
  
            @TMPINVOICESEQ float(53) = 0,   
            @BISTESTRUN bit = 1,   
            @C1 CURSOR,   
            @TMPREV varchar(100) = ' $Revision: 14$ ',   
            /*  
            *   SSMA warning messages:  
            *   O2SS0356: Conversion from NUMBER datatype can cause data loss.  
            */  
  
            @TMPCURRENTERRLEVEL float(53) = 0,   
            /*  
            *   SSMA warning messages:  
            *   O2SS0356: Conversion from NUMBER datatype can cause data loss.  
            */  
  
            @TMPMAXERRLEVEL float(53) = 0,   
            /*  
            *   SSMA warning messages:  
            *   O2SS0356: Conversion from NUMBER datatype can cause data loss.  
            */  
  
            @TMPLOADEDRECCOUNT float(53) = 0,   
            @TMPAPPNAME varchar(40) = @PAPPLICATIONNAME,   
            @TMPMSGINFO varchar(4000),   
            @TMPCRITICALMSGINFO varchar(4000),   
            /*  
            *   SSMA warning messages:  
            *   O2SS0356: Conversion from NUMBER datatype can cause data loss.  
            */  
  
            @TMPINVOICEMONTH float(53) = CAST(CONVERT(varchar(6), @PBILLPERIOD, 112) AS numeric(38, 10)),   
            @TMPTRANSACTIONDATE datetime2(0) = getdate(), ----changed from SYSDATETIME to GETDATE 09092022  
            @TMPINVOICENUMBER varchar(40),   
            @TMPERRINVOICENUMBER varchar(40),   
            @TMPBILLYEARMONTH varchar(40),   
            @TMPINVOICEDUEDATE datetime2(0),   
            @TMPCOOPNUMBER varchar(40),   
            @TMPLAWSONCUSTOMERNUMBER varchar(20),   
            @TMPMEMBER varchar(1),   
            @TMPGLACCOUNTUNIT varchar(4),   
            @TMPGLACCOUNT varchar(40),   
            @TMPGLSUBACCOUNT varchar(40),   
            @TMPDEMANDUNITS varchar(40),   
            @TMPENERGYUNITS varchar(40),   
            @TMPTOTALBILLED varchar(40),   
            /*  
            *   SSMA warning messages:  
            *   O2SS0356: Conversion from NUMBER datatype can cause data loss.  
            */  
  
            /* Critical conversion on these values as they are used to determine an unique AR record*/  
            @TMPNUMBILLYEARMONTH float(53),   
            /*  
            *   SSMA warning messages:  
            *   O2SS0356: Conversion from NUMBER datatype can cause data loss.  
            */  
  
            @TMPNUMCOOPNUMBER float(53),   
            /*  
            *   SSMA warning messages:  
            *   O2SS0356: Conversion from NUMBER datatype can cause data loss.  
            */  
  
            @TMPNUMACCOUNT float(53),   
            /*  
            *   SSMA warning messages:  
            *   O2SS0356: Conversion from NUMBER datatype can cause data loss.  
            */  
  
            @TMPNUMSUBACCOUNT float(53),   
            @TMPGETPBSINVOICEDATA varchar(4000) = '  
SELECT id.invoice_number,   
    id.bill_yearmonth,  
    id.invoice_due_date,  
    id.coop_number,   
    id.lawson_customer_number,   
    id.member,  
    COALESCE(id.gl_account_unit, ''xxxx'') AS gl_account_unit,   
    COALESCE(id.gl_account, ''xxxxxx'') AS gl_account,   
    COALESCE(id.gl_sub_account, ''xxxx'') AS gl_sub_account,   
    SUM(COALESCE(id.quantity,0) * CASE WHEN id.uom  = ''KW'' THEN 1 ELSE 0 END) AS demand_units,   
    SUM(COALESCE(id.quantity,0) * CASE WHEN id.uom = ''KWH'' THEN 1 ELSE 0 END) AS energy_units,   
    SUM(COALESCE(id.extended_price,0)) AS total_billed  
  FROM   
    (  
      SELECT *  
        FROM V_MVPBS_INVOICE_NO_ADJ  
        WHERE bill_yearmonth = 202203  
    UNION  
      SELECT *  
        FROM V_MVPBS_INVOICE_ADJ_ONLY  
        WHERE bill_yearmonth = 202203  
    ) AS id  
  WHERE id.invoice_number NOT IN (  
          SELECT DISTINCT e.invoice_number  
            FROM AR_INVOICE_ERRORS e  
            WHERE e.bill_period = CONVERT(DATE, CONVERT(VARCHAR(6), id.bill_yearmonth) + ''01'')  
              AND e.total_billed <> 0  
    )  
    AND (COALESCE(gl_account, ''X@'') + COALESCE(gl_sub_account, ''Y@'')) NOT IN (  
          SELECT DISTINCT COALESCE(e.gl_account, ''X@'') + COALESCE(e.gl_sub_account, ''Y@'')  
            FROM AR_INVOICE_ERRORS e  
            WHERE e.bill_period = CONVERT(DATE, CONVERT(VARCHAR(6), id.bill_yearmonth) + ''01'')  
              AND e.gl_account = id.gl_account  
              AND e.gl_sub_account = id.gl_sub_account  
              AND e.total_billed = 0  
    )  
  GROUP BY id.invoice_number,   
    id.bill_yearmonth,  
    id.invoice_due_date,  
    id.coop_number,   
    id.lawson_customer_number,   
    id.member,  
    COALESCE(id.gl_account_unit, ''xxxx''),   
    COALESCE(id.gl_account, ''xxxxxx''),   
    COALESCE(id.gl_sub_account, ''xxxx'')   
  ORDER BY id.bill_yearmonth,  
    id.coop_number,   
    id.invoice_number,   
    id.member,  
    COALESCE(id.gl_account_unit, ''xxxx''),   
    COALESCE(id.gl_account, ''xxxxxx''),   
    COALESCE(id.gl_sub_account, ''xxxx'')   
'  
  
         SET @TMPMSGINFO = 'P_MVPBS_AR_INTERFACE: ' + ISNULL(@TMPREV, '') + '  
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++  
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++  
          +++ Beginning for date: ' ---+ ISNULL(ssma_oracle.to_char_date(@PBILLPERIOD, 'yyyy-mm-dd'), '') + ' +++  '  
  
         IF upper(@PISTESTRUN) = 'FALSE'  
            SET @BISTESTRUN = 0  
  
         IF @BISTESTRUN != 0  
            BEGIN  
  
               SET @TMPAPPNAME = 'TEST' + ISNULL(@TMPAPPNAME, '')  
  
               SET @TMPMSGINFO = ISNULL(@TMPMSGINFO, '') + '          +++ bIsTestRun: TRUE +++' + '  
    '  
  
               DELETE FROM AR_INVOICE_ERRORS  
               WHERE BILL_PERIOD = @PBILLPERIOD  
  
               IF @@TRANCOUNT > 0  
                  COMMIT TRANSACTION   
  
            END  
         ELSE   
            SET @TMPMSGINFO = ISNULL(@TMPMSGINFO, '') + '          +++ bIsTestRun: FALSE +++' + '  
    '  
  
         DECLARE  
            @temp varchar(8000)  
  
         SET @temp = ISNULL(@TMPMSGINFO, '') + '  
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++  
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++'  
  
         /*  
         *   SSMA warning messages:  
         *   O2SS0103: A parameter was omitted in the unpackaged procedure (function) call: PERRLEVEL.  
         */  
  
         EXECUTE DBO.I_PROCESS_LOG @PMESSAGEIN = @temp, @PAPPNAME = @TMPAPPNAME  
  
         SET @TMPMSGINFO = NULL  
  
         DECLARE  
            @auxiliary_cursor_definition_sql nvarchar(max)  
  
         DECLARE  
            @auxiliary_exec_param nvarchar(max)  
  
         IF (cursor_status('variable', N'@C1') > -2)  
            DEALLOCATE @C1  
  
         /*   
         *   SSMA error messages:  
         *   O2SS0157: The OPEN...FOR statement will be converted, but the dynamic string must be converted manually.  
  
         SET @auxiliary_cursor_definition_sql = 'SET @auxiliary_tmp_cursor = CURSOR LOCAL FOR ' + @TMPGETPBSINVOICEDATA + '; OPEN @auxiliary_tmp_cursor'  
         */  
  
  
  
         /*   
         *   SSMA error messages:  
         *   O2SS0583: SSMA was unable to determine bind argument name.  
  
         SET @auxiliary_exec_param = '@auxiliary_param0 float(53) ,@auxiliary_param1 float(53) ,@auxiliary_tmp_cursor cursor OUTPUT'  
         */  
  
  
  
         EXECUTE sp_executesql   
            @auxiliary_cursor_definition_sql,   
            @auxiliary_exec_param,   
            @TMPINVOICEMONTH,   
            @TMPINVOICEMONTH,   
            @C1  OUTPUT  
  
         DECLARE  
            @temp$2 nvarchar(4000)  
  
         SET @temp$2 = 'P_MVPBS_AR_INTERFACE: Opened the cursor using tmpInvoiceMonth = ' + ISNULL(CAST(@TMPINVOICEMONTH AS nvarchar(max)), '')  
  
         /*  
         *   SSMA warning messages:  
         *   O2SS0103: A parameter was omitted in the unpackaged procedure (function) call: PERRLEVEL.  
         */  
  
         EXECUTE DBO.I_PROCESS_LOG @PMESSAGEIN = @temp$2, @PAPPNAME = @TMPAPPNAME  
  
         SET @TMPMAXERRLEVEL = 0  
  
                                DECLARE @INT INT = 1  
  
         WHILE 1 = 1  
            BEGIN  
                                                                PRINT '@WHILE ' + CONVERT(VARCHAR(5), @INT) -----Changes made on 09092022  
                                                                ---PRINT  CONVERT(VARCHAR(5), @INT)  
                                                                  
                                                                SET @INT = @INT + 1  
                                                                ----END  
  
               /*  
               *   SSMA warning messages:  
               *   O2SS0103: A parameter was omitted in the unpackaged procedure (function) call: PERRLEVEL.  
               */  
  
               /*loop though the cursor*/  
               EXECUTE DBO.I_PROCESS_LOG @PMESSAGEIN = 'P_MVPBS_AR_INTERFACE: In cursor loop: Fetching a record...', @PAPPNAME = @TMPAPPNAME  
  
               BEGIN  
  
                  BEGIN TRY  
  
                     FETCH @C1  
                         INTO   
                           @TMPINVOICENUMBER,   
                           @TMPBILLYEARMONTH,   
                           @TMPINVOICEDUEDATE,   
                           @TMPCOOPNUMBER,   
                           @TMPLAWSONCUSTOMERNUMBER,   
                           @TMPMEMBER,   
                           @TMPGLACCOUNTUNIT,   
                           @TMPGLACCOUNT,   
                           @TMPGLSUBACCOUNT,   
                           @TMPDEMANDUNITS,   
                           @TMPENERGYUNITS,   
                           @TMPTOTALBILLED  
  
                     /*  
                     *   SSMA warning messages:  
                     *   O2SS0113: The value of @@FETCH_STATUS might be changed by previous FETCH operations on other cursors, if the cursors are used simultaneously.  
                     */  
  
                     IF @@FETCH_STATUS <> 0  
                                                                                BEGIN -----------09092022  
                        BREAK  
                                                                                                END ---------------09092022  
  
                     /* no errors right now...*/  
                     SET @TMPCURRENTERRLEVEL = 0  
  
                     SET @TMPMSGINFO =   
                                                                                                                'tmpInvoiceNumber = ' +  ISNULL(@TMPINVOICENUMBER, '') +   
                                                                                                                ', tmpBillYearMonth = ' + ISNULL(@TMPBILLYEARMONTH, '')+   
                                                                                                                ', tmpInvoiceDueDate = ' + ISNULL(CAST(@TMPINVOICEDUEDATE AS nvarchar(max)), '') +   
                                                                                                                ', tmpTransactionDate = ' + ISNULL(CAST(@TMPTRANSACTIONDATE AS nvarchar(max)), '') +   
                                                                                                                ', tmpCoopNumber = ' + ISNULL(@TMPCOOPNUMBER, '') +   
                                                                                                                ', tmpLawsonCustomerNumber = ' + ISNULL(@TMPLAWSONCUSTOMERNUMBER, '') +   
                                                                                                                ', tmpMember = ' + ISNULL(@TMPMEMBER, '') +   
                                                                                                                ', tmpGLAccountUnit = ' + ISNULL(@TMPGLACCOUNTUNIT, '') +   
                                                                                                                ', tmpGLAccount = ' + ISNULL(@TMPGLACCOUNT, '') +   
                                                                                                                ', tmpGLSubAccount = ' + ISNULL(@TMPGLSUBACCOUNT, '') +   
                                                                                                                ', tmpDemandUnits = ' + ISNULL(@TMPDEMANDUNITS, '') +   
                                                                                                                ', tmpEnergyUnits = ' + ISNULL(@TMPENERGYUNITS, '') +   
                                                                                                                ', tmpTotalBilled = ' + ISNULL(@TMPTOTALBILLED, '') + ' '  
  
                     BEGIN  
  
                        BEGIN TRY  
  
                           SET @TMPCRITICALMSGINFO = ' converting TO_NUMBER(tmpBillYearMonth) '  
                           SET @TMPNUMBILLYEARMONTH = CAST(@TMPBILLYEARMONTH AS numeric(38, 10))  
                           SET @TMPCRITICALMSGINFO = ' converting TO_NUMBER(tmpCoopNumber) '  
                           SET @TMPNUMCOOPNUMBER = CAST(@TMPCOOPNUMBER AS numeric(38, 10))  
                           SET @TMPCRITICALMSGINFO = ' converting TO_NUMBER(tmpGLAccount) '  
                           SET @TMPNUMACCOUNT = CAST(@TMPGLACCOUNT AS numeric(38, 10))  
                           SET @TMPCRITICALMSGINFO = ' converting TO_NUMBER(tmpGLSubAccount) '  
                           SET @TMPNUMSUBACCOUNT = CAST(@TMPGLSUBACCOUNT AS numeric(38, 10))  
                           SET @TMPCRITICALMSGINFO = NULL  
  
                        END TRY  
  
                        BEGIN CATCH  
  
                           DECLARE  
                              @errornumber int  
  
                           SET @errornumber = ERROR_NUMBER()  
  
                           DECLARE  
                              @errormessage nvarchar(4000)  
  
                           SET @errormessage = ERROR_MESSAGE()  
  
                           DECLARE  
                              @exceptionidentifier nvarchar(4000)  
  
                           SELECT @exceptionidentifier = ssma_oracle.db_error_get_oracle_exception_id(@errormessage, @errornumber)  
  
                           IF (@exceptionidentifier LIKE N'ORA-06502%')  
                              BEGIN  
  
                                 IF @TMPTOTALBILLED = 0  
                                    SET @TMPCURRENTERRLEVEL = 10/* just a warning if there is not a billing amount...*/  
                                 ELSE   
                                    SET @TMPCURRENTERRLEVEL = 20/* CRITICAL ERROR...*/  
  
                                 IF NOT @BISTESTRUN != 0  
                                    BEGIN  
                                       IF @@TRANCOUNT > 0  
                                          ROLLBACK WORK   
                                    END  
  
                                 BEGIN  
                                    IF (@exceptionidentifier IS NOT NULL)  
                                       BEGIN  
                                          IF @errornumber = 59998  
                                             RAISERROR(59998, 16, 1, @exceptionidentifier)  
                                          ELSE   
                                             RAISERROR(59999, 16, 1, @exceptionidentifier)  
                                       END  
                                    ELSE   
                                       BEGIN  
                                          EXECUTE ssma_oracle.ssma_rethrowerror  
                                       END  
                                 END  
                                 /* Skip to the end of the loop*/  
  
                              END  
                           ELSE   
                              BEGIN  
                                 IF (@exceptionidentifier IS NOT NULL)  
                                    BEGIN  
                                       IF @errornumber = 59998  
                                          RAISERROR(59998, 16, 1, @exceptionidentifier)  
                                       ELSE   
                                          RAISERROR(59999, 16, 1, @exceptionidentifier)  
                                    END  
                                 ELSE   
                                    BEGIN  
                                       EXECUTE ssma_oracle.ssma_rethrowerror  
                                    END  
                              END  
  
                        END CATCH  
  
                     END  
  
                     BEGIN  
  
                        SELECT @TMPINVOICESEQ = count_big(LAWSON_ARINTERFACE.DR_INVOICENUMBER)  
                        FROM DBO.LAWSON_ARINTERFACE  
                        WHERE LAWSON_ARINTERFACE.DR_ACCOUNT =   
                           CASE   
                              WHEN @TMPNUMACCOUNT = '56501' THEN @TMPNUMACCOUNT  
                              ELSE LAWSON_ARINTERFACE.DR_ACCOUNT  
                           END AND LAWSON_ARINTERFACE.DR_INVOICENUMBER = @TMPINVOICENUMBER  
  
                        IF @TMPINVOICESEQ IS NULL  
                           SET @TMPINVOICESEQ = 0  
  
                     END  
  
                     DECLARE  
                        @temp$3 varchar(8000)  
  
                     SET @temp$3 = 'P_MVPBS_AR_INTERFACE: In cursor loop: Inserting into MVPBS.LAWSON_ARINTERFACE...' + ISNULL(@TMPMSGINFO, '')  
  
                     /*  
                     *   SSMA warning messages:  
                     *   O2SS0103: A parameter was omitted in the unpackaged procedure (function) call: PERRLEVEL.  
                     */  
  
                     /* If a record exists for the same invoice then increment the invoice sequence number (need a unique key)...*/  
                     EXECUTE DBO.I_PROCESS_LOG @PMESSAGEIN = @temp$3, @PAPPNAME = @TMPAPPNAME  
  
                     INSERT DBO.LAWSON_ARINTERFACE(  
                        DR_APPNAME,   
                        DR_BILLPERIOD,   
                        DR_INVOICENUMBER,   
                        DR_BILLYEARMONTH,   
                        DR_INVOICEDUEDATE,   
                        DR_TRANSACTIONDATE,   
                        DR_COOPNUMBER,   
                        DR_LAWSONCUSTOMERNUMBER,   
                        DR_MEMBER,   
                        DR_ACCOUNTUNIT,   
                        DR_ACCOUNT,   
                        DR_SUBACCOUNT,   
                        DR_DEMANDUNITS,   
                        DR_ENERGYUNITS,   
                        DR_TOTALBILLED,   
                        DR_INVOICE_SEQ)  
                        VALUES (  
                           @TMPAPPNAME,   
                           @PBILLPERIOD,   
                           @TMPINVOICENUMBER,   
                           @TMPNUMBILLYEARMONTH,   
                           @TMPINVOICEDUEDATE,   
                           @TMPTRANSACTIONDATE,   
                           @TMPNUMCOOPNUMBER,   
                           @TMPLAWSONCUSTOMERNUMBER,   
                           @TMPMEMBER,   
                           @TMPGLACCOUNTUNIT,   
                           @TMPNUMACCOUNT,   
                           CAST(@TMPGLSUBACCOUNT AS numeric(38, 10)),   
                           CAST(@TMPDEMANDUNITS AS numeric(38, 10)),   
                           CAST(@TMPENERGYUNITS AS numeric(38, 10)),   
                           CAST(@TMPTOTALBILLED AS numeric(38, 10)),   
                           @TMPINVOICESEQ)  
  
                     IF @@TRANCOUNT > 0  
                        COMMIT TRANSACTION   
  
                     IF NOT @BISTESTRUN != 0  
                        SET @TMPLOADEDRECCOUNT = @TMPLOADEDRECCOUNT + 1  
  
                  END TRY  
  
                  BEGIN CATCH  
  
                     DECLARE  
                        @errornumber$2 int  
  
                     SET @errornumber$2 = ERROR_NUMBER()  
  
                     DECLARE  
                        @errormessage$2 nvarchar(4000)  
  
                     SET @errormessage$2 = ERROR_MESSAGE()  
  
                     DECLARE  
                        @exceptionidentifier$2 nvarchar(4000)  
  
                     SELECT @exceptionidentifier$2 = ssma_oracle.db_error_get_oracle_exception_id(@errormessage$2, @errornumber$2)  
  
                     IF (@exceptionidentifier$2 LIKE @EXUSEREXIT$exception) OR (@exceptionidentifier$2 LIKE N'ORA-01012%')  
                        BEGIN  
  
                           SET @TMPERRINVOICENUMBER = @TMPINVOICENUMBER  
  
                           SET @TMPCURRENTERRLEVEL = 20/* THIS IS STILL A CRITICAL ERROR no matter what...*/  
  
                           IF @@TRANCOUNT > 0  
                              ROLLBACK WORK   
  
                           BEGIN  
                              IF (@exceptionidentifier$2 IS NOT NULL)  
                                 BEGIN  
                                    IF @errornumber$2 = 59998  
                                       RAISERROR(59998, 16, 1, @exceptionidentifier$2)  
                                    ELSE   
                                       RAISERROR(59999, 16, 1, @exceptionidentifier$2)  
                                 END  
                              ELSE   
                                 BEGIN  
                                    EXECUTE ssma_oracle.ssma_rethrowerror  
                                 END  
                           END  
  
                        END  
                     ELSE   
                        BEGIN  
  
                           IF @TMPCURRENTERRLEVEL = 20  
                              BEGIN  
  
                                 SET @TMPMAXERRLEVEL = @TMPCURRENTERRLEVEL  
  
                                 EXECUTE DBO.I_PROCESS_LOG @PMESSAGEIN = '        ***', @PAPPNAME = @TMPAPPNAME, @PERRLEVEL = @TMPCURRENTERRLEVEL  
  
                                 DECLARE  
                                    @temp$4 varchar(8000)  
  
                                 SET @temp$4 = '        P_MVPBS_AR_INTERFACE: In cursor loop:  >>>  C R I T I C A L   E R R O R  <<< !!!  
            ' + ISNULL(@TMPCRITICALMSGINFO, '') + ISNULL(@TMPMSGINFO, '')  
  
                                 EXECUTE DBO.I_PROCESS_LOG @PMESSAGEIN = @temp$4, @PAPPNAME = @TMPAPPNAME, @PERRLEVEL = @TMPCURRENTERRLEVEL  
  
                                 DECLARE  
                                    @temp$5 varchar(8000)  
  
                                 SET @temp$5 = '        P_MVPBS_AR_INTERFACE: In cursor loop: ' + ISNULL(ssma_oracle.db_error_sqlerrm_0(@exceptionidentifier$2, @errornumber$2), '')  
  
                                 EXECUTE DBO.I_PROCESS_LOG @PMESSAGEIN = @temp$5, @PAPPNAME = @TMPAPPNAME, @PERRLEVEL = @TMPCURRENTERRLEVEL  
  
                                IF NOT @BISTESTRUN != 0  
                                    BEGIN  
  
                                       IF @@TRANCOUNT > 0  
                                          ROLLBACK WORK   
  
                                       BEGIN  
                                          IF (@exceptionidentifier$2 IS NOT NULL)  
                                             BEGIN  
                                                IF @errornumber$2 = 59998  
                                                   RAISERROR(59998, 16, 1, @exceptionidentifier$2)  
                                                ELSE   
                                                   RAISERROR(59999, 16, 1, @exceptionidentifier$2)  
                                             END  
                                          ELSE   
                                             BEGIN  
                                                EXECUTE ssma_oracle.ssma_rethrowerror  
                                             END  
                                       END  
                                       /* Want the test run to continue here...*/  
  
                                    END  
  
                              END  
                           ELSE   
                              BEGIN  
  
                                 SET @TMPCURRENTERRLEVEL = 10/* warnings...*/  
  
                                 IF @TMPMAXERRLEVEL < 20  
                                    SET @TMPMAXERRLEVEL = @TMPCURRENTERRLEVEL  
  
                                 EXECUTE DBO.I_PROCESS_LOG @PMESSAGEIN = '     ***', @PAPPNAME = @TMPAPPNAME, @PERRLEVEL = @TMPCURRENTERRLEVEL  
  
                                 DECLARE  
                                    @temp$6 varchar(8000)  
  
                                 SET @temp$6 = '     P_MVPBS_AR_INTERFACE: In cursor loop: RECORD WILL NOT BE LOADED DUE TO AN ERROR!!!  
            ' + ISNULL(@TMPMSGINFO, '')  
  
                                 EXECUTE DBO.I_PROCESS_LOG @PMESSAGEIN = @temp$6, @PAPPNAME = @TMPAPPNAME, @PERRLEVEL = @TMPCURRENTERRLEVEL  
  
                                 DECLARE  
                                    @temp$7 varchar(8000)  
  
                                 SET @temp$7 = '     P_MVPBS_AR_INTERFACE: In cursor loop: ' + ISNULL(ssma_oracle.db_error_sqlerrm_0(@exceptionidentifier$2, @errornumber$2), '')  
  
                                 EXECUTE DBO.I_PROCESS_LOG @PMESSAGEIN = @temp$7, @PAPPNAME = @TMPAPPNAME, @PERRLEVEL = @TMPCURRENTERRLEVEL  
  
                              END  
  
                           EXECUTE DBO.I_PROCESS_LOG @PMESSAGEIN = '     ***', @PAPPNAME = @TMPAPPNAME, @PERRLEVEL = @TMPCURRENTERRLEVEL  
  
                           IF @BISTESTRUN != 0  
                              BEGIN  
  
                                 DECLARE  
                                    @temp$8 varchar(8000),@PBILL_PERIOD datetime  
  
                                 SET @temp$8 = ISNULL(@TMPCRITICALMSGINFO, '') + ': ' + ISNULL(ssma_oracle.db_error_sqlerrm_0(@exceptionidentifier$2, @errornumber$2), '')  
  
                                 EXECUTE DBO.I_AR_INVOICE_ERRORS   
                                    @bill_period= @PBILL_PERIOD,   
                                    @INVOICE_NUMBER = @TMPINVOICENUMBER,   
                                    @BILL_YEARMONTH= @TMPBILLYEARMONTH,   
                                    @INVOICE_DUE_DATE = @TMPINVOICEDUEDATE,   
                                    @TRANSACTION_DATE = @TMPTRANSACTIONDATE,   
                                    @COOP_NUMBER= @TMPCOOPNUMBER,   
                                    @LAWSON_CUSTOMER_NUMBER = @TMPLAWSONCUSTOMERNUMBER,   
                                    @MEMBER = @TMPMEMBER,   
                                    @GL_ACCOUNT_UNIT= @TMPGLACCOUNTUNIT,   
                                    @GL_ACCOUNT= @TMPGLACCOUNT,   
                                    @GL_SUB_ACCOUNT = @TMPGLSUBACCOUNT,   
                                    @DEMAND_UNITS= @TMPDEMANDUNITS,   
                                    @ENERGY_UNITS = @TMPENERGYUNITS,   
                                    @TOTAL_BILLED = @TMPTOTALBILLED,   
                                    @ERR_LEVEL= @TMPCURRENTERRLEVEL,   
                                    @ERR_MSG= @temp$8  
  
                              END  
  
                        END  
  
                  END CATCH  
  
               END  
  
            END  
         /*loop though the cursor*/  
  
                                PRINT 'YAY!!!!!!!!!!!! WHILE DONE'  
  
         IF (NOT @BISTESTRUN != 0 AND (@TMPLOADEDRECCOUNT = 0))  
            BEGIN  
  
               SET @TMPMAXERRLEVEL = 20  
  
               BEGIN  
  
                  DECLARE  
                     @db_raise_application_error_message nvarchar(4000)  
  
                  SET @db_raise_application_error_message = N'ORA' + CAST(-20110 AS nvarchar) + N': ' + N'NO invoice records found in MVPBS'  
  
                  RAISERROR(59998, 16, 1, @db_raise_application_error_message)  
  
               END  
  
            END  
  
         IF @BISTESTRUN != 0  
            BEGIN  
               IF @@TRANCOUNT > 0  
                  ROLLBACK WORK   
               /* Don't save anything from a test run!*/  
            END  
         ELSE   
            BEGIN  
               IF @@TRANCOUNT > 0  
                  COMMIT TRANSACTION   
            END  
  
         IF (@TMPMAXERRLEVEL > 0) AND (@TMPMAXERRLEVEL < 20)  
            EXECUTE DBO.I_PROCESS_LOG @PMESSAGEIN = 'P_MVPBS_AR_INTERFACE: Finished, but with warnings -- please review the log entries!!!', @PAPPNAME = @TMPAPPNAME, @PERRLEVEL = @TMPMAXERRLEVEL  
         ELSE   
            IF (@TMPMAXERRLEVEL >= 20)  
               EXECUTE DBO.I_PROCESS_LOG @PMESSAGEIN = 'P_MVPBS_AR_INTERFACE: Finished, but with CRITICAL ERRORS -

Answer 1

Maybe you must add BREAK (or an appropriate code that exits the loop and the procedure) before each END CATCH that is inside the WHILE loop.

Answer 2

@Viorel Thanks I added BREAK line 287. I think the issue is some where here
DECLARE @xcl INT = 1

         WHILE 1 = 1  
         
            BEGIN  
                       
                           PRINT ('@INT ' + CONVERT(VARCHAR(5), @INT)) -----Changes made on 09092022  
                           BEGIN  
                           BREAK  
                           END  
                             
                           SET @INT = @INT + 1  
  

Answer 3

Do I understand this right that this procedure has been generated for the SQL Server Migration Assistent for Oracle, and it is based on an original Orcale procedure?

In any case, the procedure calls for a complete overhaul. What is best practice on Oracle may not be best practiice on SQL Server and vice versa. Actaully, rather than an overhaul, the best may be to start from the beginning, and this may include changing nested procedures as well, so that they can work with set-based input. Yes, I know that it is a lot work. But it may pay off, not the lest in performance.

Anyway, I did not feel like reading through all that code - after all, I'm answering forum questions in my free time - but I have one suggestion: on line 288, change LOCAL to STATIC. The default cursor type is dynamic, which means that the cursor is evaluated on every FETCH. Which could have some interesting results. And be slow.