Advertisement
jotaced

no cursor use

Jul 4th, 2013
280
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 1.81 KB | None | 0 0
  1. -- Create a table variable to store user data
  2. DECLARE @myTable TABLE
  3. (
  4.     UserID INT IDENTITY(1,1),
  5.     UserName VARCHAR(50),
  6.     Password VARCHAR(50),
  7.     Email VARCHAR(50)
  8. )
  9.  
  10. -- Insert some data to table to work on that data
  11.  
  12. INSERT INTO @myTable(UserName, Password, Email)
  13. VALUES ('Jack', 'JackPwd', 'jack@gmail.com')
  14.  
  15. INSERT INTO @myTable(UserName, Password, Email)
  16. VALUES ('Raj', 'RajPwd', 'raj@gmail.com')
  17.  
  18. INSERT INTO @myTable(UserName, Password, Email)
  19. VALUES ('smith', 'smithPwd', 'smith@gmail.com')
  20.  
  21. INSERT INTO @myTable(UserName, Password, Email)
  22. VALUES ('Tom', 'tomPwd', 'tom@gmail.com')
  23.  
  24. -- Get the number of rows in the looping table
  25. DECLARE @RowCount INT
  26. SET @RowCount = (SELECT COUNT(UserID) FROM @myTable)
  27.  
  28.  
  29. -- Declare an iterator
  30. DECLARE @I INT
  31. -- Initialize the iterator
  32. SET @I = 1
  33.  
  34.  
  35. -- Loop through the rows of a table @myTable
  36. WHILE (@I <= @RowCount)
  37. BEGIN
  38.         -- Declare variables to hold the data which we get after looping each record
  39.      
  40.         DECLARE @iUserName VARCHAR(50), @iPassword VARCHAR(50), @iEmail VARCHAR(50)    
  41.        
  42.         -- Get the data from table and set to variables
  43.         SELECT @iUserName = UserName, @iPassword = Password, @iEmail = Email FROM @myTable WHERE UserID = @I
  44.         -- Display the looped data
  45.         PRINT 'Row No = ' + CONVERT(VARCHAR(2), @I)
  46.         PRINT 'UserName = ' + @iUserName + ', Password = ' + @iPassword + ' Email = '+ @iEmail
  47.         -- Increment the iterator
  48.         SET @I = @I  + 1
  49. END
  50.  
  51. Here IS the output OF the above query.
  52.  
  53. ROW No = 1
  54. UserName = Jack, Password = JackPwd Email = jack@gmail.com
  55. ROW No = 2
  56. UserName = Raj, Password = RajPwd Email = raj@gmail.com
  57. ROW No = 3
  58. UserName = smith, Password = smithPwd Email = smith@gmail.com
  59. ROW No = 4
  60. UserName = Tom, Password = tomPwd Email = tom@gmail.com
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement