Posted By

mafro on 03/30/07


Tagged

cursor


Versions (?)

Cursor


 / Published in: SQL
 

Basic efficient (!) cursor for looping a set. LOCAL is scope, FASTFORWARD means fetch next is the only allowed command, and it also implies readonly.

  1. SET nocount ON
  2.  
  3. declare @asset_id varchar(40)
  4. declare @suffix varchar(4)
  5. declare @count int
  6. SET @count = 0
  7.  
  8. declare iterate cursor LOCAL FAST_FORWARD FOR
  9. SELECT DISTINCT asset_id, suffix FROM tbl_src
  10.  
  11. open iterate
  12. fetch next FROM iterate INTO @asset_id, @suffix
  13.  
  14. --loop all items
  15. while @@fetch_status = 0
  16. begin
  17. --do something with current item
  18. INSERT INTO tbl_asset (asset_id, suffix, can_have_multiple)
  19. VALUES (@asset_id, @suffix, 0)
  20.  
  21. IF @@ROWCOUNT > 0
  22. begin
  23. SET @count = @count + 1
  24. end
  25.  
  26. --grab next item
  27. fetch next FROM iterate INTO @asset_id, @suffix
  28. end
  29.  
  30. close iterate
  31. deallocate iterate
  32.  
  33. print ''
  34. print '(' + convert(varchar,@count) + ' row(s) affected)'
  35. print ''
  36.  
  37. SET nocount off

Report this snippet  

You need to login to post a comment.