How to avoid using while/cursor to achieve the same query results?

Question

I want to avoid looping from the below query.Now it takes long to retrieve the results from a table with 100000 records.

        @iMAX int, 
        @rowId int,
        @currCode varchar(20),
        @lastCode varchar(20),
        @amount money,
        @total money

  SET @rowId=1
  SET @iMAX=(Select count(RowId) from Table1)

WHILE(@rowId<=@iMAX)
  -- WHILE iteration
  BEGIN
        SELECT @Code=Code,@amount=Amount FROM Table1 WHERE RowId=@rowId
        if @lastCode is null or @lastCode <> @currCode
        begin 
              set @total = 0 
              set @lastCode = @currCode 
        end
        set @total = @total + @amount
        update Table1 set Balance = OpBalance + @total where RowId = @rowId

        SET @rowId=@rowId+1
  END

Could you add what you are trying to accomplish, your schema and some sample data and expected output? — HoneyBadger, Dec 08 '15 at 14:12
You do an if statement on @lastCode but you don't set it. Is the setting just missing? Otherwise it's always null. — Dane, Dec 08 '15 at 14:19

score 0 · Answer 1 · answered Dec 08 '15 at 14:27

0

;WITH cte AS 
(
    SELECT *
        , total = SUM(Amount) OVER (ORDER BY RowId)
        , prevCode = LAG(Code) OVER (ORDER BY RowId)
    FROM dbo.Table1
)
UPDATE cte
SET Balance = OpBalance +
    CASE WHEN prevCode IS NULL OR prevCode != Code 
        THEN 0
        ELSE total
    END

answered Dec 08 '15 at 14:27

Devart

119,203
23
166
186

How this section works in your query set total = total + amount – KrishOnline Dec 09 '15 at 07:47

How to avoid using while/cursor to achieve the same query results?

1 Answers1