code_junkie

Why are relational set-based queries better than cursors?

Nov 10th, 2011
114
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 0.77 KB | None | 0 0
  1. --Cursor
  2. DECLARE @phoneNumber char(7)
  3. DECLARE c CURSOR LOCAL FAST_FORWARD FOR
  4. SELECT PhoneNumber FROM Customer WHERE AreaCode IS NULL
  5. OPEN c
  6. FETCH NEXT FROM c INTO @phoneNumber
  7. WHILE @@FETCH_STATUS = 0 BEGIN
  8. DECLARE @exchange char(3), @areaCode char(3)
  9. SELECT @exchange = LEFT(@phoneNumber, 3)
  10.  
  11. SELECT @areaCode = AreaCode
  12. FROM AreaCode_Exchange
  13. WHERE Exchange = @exchange
  14.  
  15. IF @areaCode IS NOT NULL BEGIN
  16. UPDATE Customer SET AreaCode = @areaCode
  17. WHERE CURRENT OF c
  18. END
  19. FETCH NEXT FROM c INTO @phoneNumber
  20. END
  21. CLOSE c
  22. DEALLOCATE c
  23. END
  24.  
  25. --Set
  26. UPDATE Customer SET
  27. AreaCode = AreaCode_Exchange.AreaCode
  28. FROM Customer
  29. JOIN AreaCode_Exchange ON
  30. LEFT(Customer.PhoneNumber, 3) = AreaCode_Exchange.Exchange
  31. WHERE
  32. Customer.AreaCode IS NULL
Add Comment
Please, Sign In to add comment