title: SELECT() next row in value assigning statement?
body: <p>I'm trying to select rows one by one in a while loop in SQL Server. And I'm using the only way I know to select the next row using <code>ROW_NUMBER</code> function. But I also have to assign values to t-sql variables in the same <code>SELECT</code> statement thus I'm getting the following error;</p>
  
  <blockquote>
    <p>A SELECT statement that assigns a value to a variable must not
    be combined with data-retrieval operations.</p>
  </blockquote>
  
  <p>Anyway my code is like this;</p>
  
  <pre><code>   WHILE (@i &lt; 5)
     BEGIN
          SELECT -- This is where the error occurs
              @resultId = video.id, -- this is the value assigning i need to do
              ROW_NUMBER() OVER (ORDER BY dateAdded DESC) AS ROWID
          FROM videoTest.dbo.video
          LEFT JOIN videoTest.dbo.aspnet_Users
          ON video.userId=aspnet_Users.UserId
          WHERE aspnet_Users.UserName=@searchUserName AND ROWID = @i
  
     -- Processing @resultId       
          SELECT
              compilationId
          FROM videoTest.dbo.comp
          WHERE vidId = @resultId -- i need the id from above
          ....
          ....
          ..... 
          ......
  
     @i = @i + 1
     END
  </code></pre>
  
  <p>I would be much appreciated if you can show me way to select next in an alternative way without getting this error, or a way to solve this error in my current code.
  Thanks.</p>