SQL Server Performance Forum – Threads Archive

Cursor issue

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 [email protected] AND [email protected] OPEN TCUR
FETCH NEXT FROM TCUR INTO @IDCUOTA, @CUOTAT WHILE @@FETCH_STATUS=0 BEGIN IF @TOTFACT<@CUOTAT BEGIN
SET @[email protected] UPDATE CUOTAST SET [email protected], IDSTATUS=3 WHERE [email protected] AND IDFACTURA IS NULL AND [email protected] AND IDSTATUS=1 INSERT INTO CUOTAST
SELECT @IDCUOTA + ‘A’, IDUNIDAD, FECHAAFP, IDMARCA, IDBIDS, IDFRANJA, IDTIPOVEHICULO, @IDCUOTA, @DIF, IDFACTURA, 2, COMENTARIOS, IDMODIFICACION
FROM CUOTAST WHERE [email protected]
INSERT INTO CUOTAST
SELECT @IDCUOTA + ‘A’, IDUNIDAD, FECHAAFP, IDMARCA, IDBIDS, IDFRANJA, IDTIPOVEHICULO, @IDCUOTA, @CUOTAT – @DIF, NULL, 1, COMENTARIOS, IDMODIFICACION FROM CUOTAST WHERE [email protected]
BREAK
END
IF @TOTFACT> @CUOTAT BEGIN
IF @SUBT < @TOTFACT BEGIN
IF @DIF > @CUOTAT BEGIN
UPDATE CUOTAST SET [email protected], IDSTATUS=2 WHERE [email protected] AND IDFACTURA IS NULL
AND [email protected] IDSTATUS=1
SET @[email protected] + @CUOTAT
SET @[email protected]@SUBT
END ELSE
IF @DIF < @CUOTAT BEGIN
UPDATE CUOTAST SET [email protected], IDSTATUS=3 WHERE [email protected] AND IDFACTURA IS NULL
AND [email protected] AND IDSTATUS=1
INSERT INTO CUOTAST
SELECT @IDCUOTA + ‘A’, IDUNIDAD, FECHAAFP, IDMARCA, IDBIDS, IDFRANJA, IDTIPOVEHICULO, @IDCUOTA,
@DIF, IDFACTURA, 2, COMENTARIOS, IDMODIFICACION
FROM CUOTAST WHERE [email protected] INSERT INTO CUOTAST
SELECT @IDCUOTA + ‘A’, IDUNIDAD, FECHAAFP, IDMARCA, IDBIDS, IDFRANJA, IDTIPOVEHICULO, @IDCUOTA, @CUOTAT – @DIF, NULL, 1, COMENTARIOS, IDMODIFICACION
FROM CUOTAST WHERE [email protected]
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 [email protected], IDSTATUS=3
WHERE [email protected] AND IDFACTURA IS NULL
AND CUOTA> @TOTFACT AND IDSTATUS=1
AND [email protected] INSERT INTO CUOTAST
SELECT IDCUOTA + ‘A’, IDUNIDAD, FECHAAFP, IDMARCA, IDBIDS, IDFRANJA,
IDTIPOVEHICULO, @IDCUOTA, @TOTFACT, IDFACTURA, 2, COMENTARIOS, IDMODIFICACION
FROM CUOTAST WHERE [email protected] AND CUOTA> @TOTFACT AND [email protected]
MohammedU.
Moderator
SQL-Server-Performance.com

---

]]>