Cursor Looping SQL Server

Question

I need some help with a SQL Server cursor:

     DECLARE @iTerminalID varchar(100)
     DECLARE @iID int
     DECLARE @iDateTB date
     DECLARE @iNom bigint
     DECLARE @iTBtoEOD bigint
     DECLARE @iSal bigint
     DECLARE @iOldNom bigint
     DECLARE @iOldSal bigint
     DECLARE @check int

     DECLARE main_cursor CURSOR FOR
     SELECT
       TerminalId
     FROM AA
     WHERE Nom IS NULL
     GROUP BY TerminalId
     ORDER BY TerminalId

     OPEN main_cursor
     FETCH NEXT
     FROM main_cursor INTO @iTerminalID
     WHILE (@@fetch_status = 0)

     BEGIN

       SET @iOldSal = 0

       DECLARE detail_cursor CURSOR FOR
       SELECT
         ROW_NUMBER() OVER (ORDER BY TerminalId, DateTB) NoID,
         DateTB,
         Nomi,
         TBtoEOD,
         Sal
       FROM AA
       WHERE TerminalId = @iTerminalID

       OPEN detail_cursor
       FETCH NEXT
       FROM detail_cursor INTO @iID, @iDateTB, @iNom, @iTBtoEOD, @iSal
       WHILE (@@fetch_status = 0)
       BEGIN
         IF  @iNom IS NULL AND @iID = 1        
         BEGIN
           UPDATE AA
           SET Nom = 0
           WHERE TerminalId = @iTerminalID
           AND CONVERT(date, TanggalTB) = CONVERT(date, @iDateTB)        
           SET @iOldSal = abs(@iTBtoEOD)
         END
         ELSE IF @iNom IS NULL
           AND @iID <> 1
           --AND @iOldSal <> 0
         BEGIN
           UPDATE AA
           SET Nom = @iOldSal
           WHERE TerminalId = @iTerminalID
           AND CONVERT(date, DateTB) = CONVERT(date, @iDateTB)
           PRINT concat(@iTerminalID, '----', @iDateTB)
           SET @iOldSal = @iNom - @iTBtoEOD
         END
         ELSE
         BEGIN
           SET @iOldSal = @iNom - @iTBtoEOD
         END

         FETCH detail_cursor INTO @iID, @iDateTB, @iNom, @iTBtoEOD, @iSal
       END
       CLOSE detail_cursor
       DEALLOCATE detail_cursor

       FETCH main_cursor INTO @iTerminalID
     END
     CLOSE main_cursor
     DEALLOCATE main_cursor

I have 2 cursors The first cursor for looping to the detailed data after i get the detail of the data that I have, I will perform the update based on the calculation that I have written

the problem here is not done until the update process is completed by the cursor to 2

Answer 1

As marc_s suggested it is best to get rid of SQL cursor on a SQL Server development solution unless you have no other choice.

I have prepared following T-SQL Update command where no cursor is used

UPDATE AA
set 
    Nomi = case when ( (NewAA.NoID = 1) and (AA.Nomi is null) ) 
            then 0 
            when ( (NewAA.NoID > 1) and (AA.Nomi is null) )
            then (select sum(t.Nomi) from AA t where t.DateTB < AA.DateTB and t.TerminalId = AA.TerminalId)
            else AA.Nomi
        end 
FROM AA
Inner Join (
    SELECT
        PK_Field,
        NoID = ROW_NUMBER() OVER (Partition By TerminalId ORDER BY DateTB),
        DateTB,
        Nomi,
        TBtoEOD,
        Sal
    FROM AA
) NewAA 
    on AA.PK_Field = NewAA.PK_Field

Please note that I have used the Primary Key field named PK_Field. I hope you have primary key on your table AA

I'm not sure about the Update statement details, please test above sql code on a test environment first

Answer 2

review diff on every step

Step 1

• specify type/direction of cursor
• not group_by but distinct, don't confuse yourself
• in row_number unnecessary sorting by TerminalId while filtering by single @iTerminalId
• save same syntax for fetches and other statements
• looks like you need row_numbered rows in ascending order - you have to specify order_by explicitly

DECLARE
    @main_cursor    cursor,
    @detail_cursor  cursor

 SET @main_cursor = CURSOR FAST_FORWARD FOR
 SELECT DISTINCT
   TerminalId
 FROM AA
 WHERE Nom IS NULL
 ORDER BY TerminalId

 OPEN @main_cursor

 FETCH NEXT FROM @main_cursor
 INTO @iTerminalID

 WHILE (@@fetch_status = 0)
 BEGIN
   SET @iOldSal = 0

   SET @detail_cursor = CURSOR FAST_FORWARD FOR
   SELECT
     ROW_NUMBER() OVER (ORDER BY DateTB) as NoID,
     DateTB, Nomi, TBtoEOD, Sal
   FROM AA
   WHERE TerminalId = @iTerminalID
   ORDER BY NoID

   OPEN @detail_cursor

   FETCH NEXT FROM @detail_cursor
   INTO @iID, @iDateTB, @iNom, @iTBtoEOD, @iSal

   WHILE (@@fetch_status = 0)
   BEGIN
     ... same inner part for now

     FETCH NEXT FROM @detail_cursor
     INTO @iID, @iDateTB, @iNom, @iTBtoEOD, @iSal
   END
   CLOSE @detail_cursor
   DEALLOCATE @detail_cursor

   FETCH NEXT FROM @main_cursor
   INTO @iTerminalID
 END
 CLOSE @main_cursor
 DEALLOCATE @main_cursor

Step 2

• outer loop does nothing but zeroing @iOldSal
• another thing - row_number starting with 1 for every TerminalID
• so all the data needed in right order may be obtained with single query

SET @detail_cursor = CURSOR FAST_FORWARD FOR
SELECT
  a.TerminalId,
  ROW_NUMBER() OVER (PARTITION BY a.TerminalId ORDER BY a.DateTB) as NoID,
  a.DateTB, a.Nomi, a.TBtoEOD, a.Sal
FROM AA a
ORDER BY a.TerminalId, NoID

OPEN @detail_cursor

FETCH NEXT FROM @detail_cursor
INTO @TerminalId, @iID, @iDateTB, @iNom, @iTBtoEOD, @iSal

WHILE (@@fetch_status = 0)
BEGIN
  if @iID = 1
    SET @iOldSal = 0

  ... same inner part for now

  FETCH NEXT FROM @detail_cursor
  INTO @TerminalId, @iID, @iDateTB, @iNom, @iTBtoEOD, @iSal
END
CLOSE @detail_cursor
DEALLOCATE @detail_cursor

Step 3

Following changes I'm performing assuming you are renumbering rows per each terminal in DateTB order and that you have a typo in first update (TanggalTB column instead of DateTB) and that TBtoEOD is some kind of offset which is always negative and that every update affects only one row. These assumptions may be incorrect - feel free to note in comments.

• @iOldSal computation is always the same except @iNum=NULL which can be handled with help of IsNull()
• when @iID=1 @iOldSal is always 0 so first IF makes no sense - it's the same as second
• when @iNom is NULL @iNom - @iTBtoEOD will return NULL - you have to initialize it
• cursor processing means you really have a cursor positioned on currently processed row so you don't have to locate it again in update statement; but you need to modify cursor type (FAST_FORWARD contains READONLY) to FORWARD_ONLY (probably with FOR_UPDATE - just can't remember; could be sqlserver will not allow it to be "FOR_UPDATE" because of window function, I did not check)
• many variables become unnecessary because we refer row values directly and don't need to repeat searching of currently processed row

SET @detail_cursor = CURSOR FORWARD_ONLY, FOR_UPDATE FOR
SELECT
  ROW_NUMBER() OVER (PARTITION BY a.TerminalId ORDER BY a.DateTB) as NoID,
  a.Nom
FROM AA a
ORDER BY a.TerminalId, NoID

FETCH NEXT FROM @detail_cursor
INTO @iID, @iNom

WHILE (@@fetch_status = 0)
BEGIN
  IF @iID = 1
    SET @iOldSal = 0

  UPDATE a SET
    Nom = IsNull(a.Nom, @iOldSal),
    @iOldSal = a.Nom - a.TBtoEOD
  FROM AA a
  WHERE CURRENT OF @detail_cursor

  FETCH NEXT FROM @detail_cursor
  INTO @iID, @iNom
END
CLOSE @detail_cursor
DEALLOCATE @detail_cursor

Step 4

So you are just setting a number to rows which don't have it based on prior numbered row? Not sure if I understood everything right but it seems to me that this task could be accomplished with code like this:

;WITH cteAA as
(
  SELECT
    a.TerminalID, a.TBtoEOD, a.Nom,
    ROW_NUMBER() OVER (PARTITION BY a.TerminalId ORDER BY a.DateTB, a.Nom) as NoID
  FROM AA a
),
cteAANums as
(
  SELECT a.TerminalID, a.NoID, IsNull(a.Nom, 0) as Nom, a.TBtoEOD
  FROM cteAA a
  WHERE a.NoID = 1

  UNION ALL

  SELECT a.TerminalID, a.NoID, IsNull(a.Nom, n.Nom-n.TBtoEOD) as Nom, a.TBtoEOD
  FROM cteAA a
  INNER JOIN cteAANums n on n.TerminalID = a.TerminalID
  WHERE a.NoID = n.NoID + 1
)
UPDATE a SET
  Nom = n.Nom
FROM AA a
INNER JOIN cteAANums n on n.TerminalID = a.TerminalID and a.DateTB = n.DateTB

It cannot be accomplished without self-joins and regarding other circumstances cursor is probably not a bad alternative. But not the only one for sure.