CODEWITHSUNDEEP
sql-servert-sqlcursor
Gavin Holyday
Gavin Holyday

Reputation: 69

sql cursor repeating

I'm having trouble with a cursor I'm using in SQL. This is my first attempt at using one, but it seems to be infinitely repeating the first entry rather than iterating through and I'm unsure why this is, as my understanding was that the fetch next statement should continue through the loop.

The output shows as:

(235 row(s) affected)
95208

(1 row(s) affected)

(1 row(s) affected)
95208

(1 row(s) affected)

(1 row(s) affected)
95208

(1 row(s) affected)
...

Any help would be greatly appreciated.

Here is my script:

Disable trigger tr_ttcard_ForUpdate on ttcard

-- Loop through backupBoards
declare @getTBoard cursor
declare @getTCard cursor    
declare @tcardToUpdateId int

select ttboard.id into #TempTBoards from ttboard 
join newtbackuptboards on newtbackuptboards.TBoardId = ttboard.id
where ttboard.IsActive = 0 and newtbackuptboards.id is not null

-- Update each existing tcard in backupBoards to new mappings
set @getTCard = cursor for
select ttcard.id from ttcard 
join #TempTBoards on #TempTBoards.id = ttcard.parentboard
where #TempTBoards.id is not null

open @getTCard
fetch next from @getTCard into @tcardToUpdateId
while @@FETCH_STATUS = 0
begin
    print cast(@tcardToUpdateId as nvarchar(max))
    -- create new backupCardMap linking tcardId and backupId
    insert into tbackupTCardMap (backupTCardId, backupId)
    values(
        @tcardToUpdateId,
        (select newtbackuptboards.id from newtbackuptboards 
         join ttboard on ttboard.id = newtbackuptboards.TBoardId
         join ttcard on ttcard.parentboard = ttboard.id
         where ttcard.id = @tcardToUpdateId)
    )

    update ttcard
    set backupOfTCard = @tcardToUpdateId
    where id = @tcardToUpdateId

end
close @getTCard
deallocate @getTCard

drop table #TempTBoards
go
-- Enable trigger for tcard changes
Enable trigger tr_ttcard_ForUpdate on ttcard

Upvotes: 0

Views: 1443

Answers (3)

M.Ali
M.Ali

Reputation: 69514

I would replace this performance killer cursor with simple INSERT and UPDATE statements, something like this....

BEGIN TRANSACTION;

-- Update records
update ttcard
set backupOfTCard = ttboard.id
from ttboard 
join newtbackuptboards on newtbackuptboards.TBoardId = ttboard.id
join ttcard            on ttcard.parentboard = ttboard.id 
where ttboard.IsActive = 0 
  AND newtbackuptboards.id IS NOT NULL

-- Insert records
insert into tbackupTCardMap (backupTCardId, backupId)
SELECT ttboard.id , newtbackuptboards.id
from ttboard 
join newtbackuptboards on newtbackuptboards.TBoardId = ttboard.id
join ttcard            on ttcard.parentboard = ttboard.id 
where ttboard.IsActive = 0 
  AND newtbackuptboards.id IS NOT NULL

COMMIT TRANSACTION;

Upvotes: 1

A_Sk
A_Sk

Reputation: 4630

you missed the

fetch next from @getTCard into @tcardToUpdateId

See This

Try:

open @getTCard
fetch next from @getTCard into @tcardToUpdateId
while @@FETCH_STATUS = 0
begin
    print cast(@tcardToUpdateId as nvarchar(max))
    -- create new backupCardMap linking tcardId and backupId
    insert into tbackupTCardMap (backupTCardId, backupId)
    values(
        @tcardToUpdateId,
        (select newtbackuptboards.id from newtbackuptboards 
         join ttboard on ttboard.id = newtbackuptboards.TBoardId
         join ttcard on ttcard.parentboard = ttboard.id
         where ttcard.id = @tcardToUpdateId)
    )

    update ttcard
    set backupOfTCard = @tcardToUpdateId
    where id = @tcardToUpdateId

fetch next from @getTCard into @tcardToUpdateId --what you missed 
end
close @getTCard
deallocate @getTCard

Upvotes: 0

Juan-Carlos
Juan-Carlos

Reputation: 377

It appears you are doing a priming fetch before beginning the cursor loop, but not fetching again INSIDE the loop. Add another fetch after your update statement but before the end statement.

update

Fetch Next

end close

Upvotes: 2

Related Questions