I have Invoices table and a Quotes table, each record in the quotes table has a fixed amount quote, I have a cursor that updates each quote depending of the total of the invoice amount, and if one quotes has to be splitted in order for it to, grouped with the other ones, sums the total of the invoice amount, it has to be done, the process works fine but it takes a long time in the execution. Here is the code: DECLARE TCUR CURSOR FOR SELECT IDCUOTA, CUOTA FROM CUOTAST WHERE IDSTATUS=1 AND IDBIDS=@IDBIDS AND IDFRANJA=@IDFRANJA OPEN TCUR FETCH NEXT FROM TCUR INTO @IDCUOTA, @CUOTAT WHILE @@FETCH_STATUS=0 BEGIN IF @TOTFACT<@CUOTAT BEGIN SET @DIF=@TOTFACT UPDATE CUOTAST SET IDFACTURA=@IDFACTURA, IDSTATUS=3 WHERE IDBIDS=@IDBIDS AND IDFACTURA IS NULL AND IDCUOTA=@IDCUOTA AND IDSTATUS=1 INSERT INTO CUOTAST SELECT @IDCUOTA + 'A', IDUNIDAD, FECHAAFP, IDMARCA, IDBIDS, IDFRANJA, IDTIPOVEHICULO, @IDCUOTA, @DIF, IDFACTURA, 2, COMENTARIOS, IDMODIFICACION FROM CUOTAST WHERE IDCUOTA=@IDCUOTA INSERT INTO CUOTAST SELECT @IDCUOTA + 'A', IDUNIDAD, FECHAAFP, IDMARCA, IDBIDS, IDFRANJA, IDTIPOVEHICULO, @IDCUOTA, @CUOTAT - @DIF, NULL, 1, COMENTARIOS, IDMODIFICACION FROM CUOTAST WHERE IDCUOTA=@IDCUOTA BREAK END IF @TOTFACT> @CUOTAT BEGIN IF @SUBT < @TOTFACT BEGIN IF @DIF > @CUOTAT BEGIN UPDATE CUOTAST SET IDFACTURA=@IDFACTURA, IDSTATUS=2 WHERE IDBIDS=@IDBIDS AND IDFACTURA IS NULL AND IDCUOTA=@IDCUOTAAND IDSTATUS=1 SET @SUBT=@SUBT + @CUOTAT SET @DIF=@TOTFACT-@SUBT END ELSE IF @DIF < @CUOTAT BEGIN UPDATE CUOTAST SET IDFACTURA=@IDFACTURA, IDSTATUS=3 WHERE IDBIDS=@IDBIDS AND IDFACTURA IS NULL AND IDCUOTA=@IDCUOTA AND IDSTATUS=1 INSERT INTO CUOTAST SELECT @IDCUOTA + 'A', IDUNIDAD, FECHAAFP, IDMARCA, IDBIDS, IDFRANJA, IDTIPOVEHICULO, @IDCUOTA, @DIF, IDFACTURA, 2, COMENTARIOS, IDMODIFICACION FROM CUOTAST WHERE IDCUOTA=@IDCUOTA INSERT INTO CUOTAST SELECT @IDCUOTA + 'A', IDUNIDAD, FECHAAFP, IDMARCA, IDBIDS, IDFRANJA, IDTIPOVEHICULO, @IDCUOTA, @CUOTAT - @DIF, NULL, 1, COMENTARIOS, IDMODIFICACION FROM CUOTAST WHERE IDCUOTA=@IDCUOTA BREAK END END END FETCH NEXT FROM TCUR INTO @IDCUOTA, @CUOTAT END CLOSE TCUR DEALLOCATE TCUR Can some one show some pointers in doing without the cursor?
Try using inserting your CURSOR select into temp table and join the temp table for update and insert OR Try to figure it out the all condions and change your WHERE clause in your update and insert...like the following once... UPDATE CUOTAST SET IDFACTURA=@IDFACTURA, IDSTATUS=3 WHERE IDBIDS=@IDBIDS AND IDFACTURA IS NULL AND CUOTA> @TOTFACT AND IDSTATUS=1 AND IDFRANJA=@IDFRANJA INSERT INTO CUOTAST SELECT IDCUOTA + 'A', IDUNIDAD, FECHAAFP, IDMARCA, IDBIDS, IDFRANJA, IDTIPOVEHICULO, @IDCUOTA, @TOTFACT, IDFACTURA, 2, COMENTARIOS, IDMODIFICACION FROM CUOTAST WHERE IDFRANJA=@IDFRANJA AND CUOTA> @TOTFACT AND IDFRANJA=@IDFRANJA MohammedU. Moderator SQL-Server-Performance.com