title: Run SQL Server function against each row in table
body: <p>I have a function that which iterates "for each row" in the table. When it is run it should look at each row, pull in the related SET values for that row and run the function which in turns returns a result and updates the correct row with the correct value. What is happening is that it is running and returning the value for the very last row and updating all the row with that value. Any ideas what I'm doing wrong? </p>
  
  <pre><code>SELECT 
       RowNum = ROW_NUMBER() OVER(ORDER BY ID)
       ,*
  INTO #Geo
  FROM DDDG
  
  DECLARE @MaxRownum INT
  SET @MaxRownum = (SELECT MAX(RowNum) FROM #Geo)
  
  DECLARE @Iter INT
  SET @Iter = (SELECT MIN(RowNum) FROM #Geo)
  
  WHILE @Iter &lt;= @MaxRownum
  BEGIN
       SELECT *
       FROM #Geo
       WHERE RowNum = @Iter
  
      DECLARE @address nvarchar (100);
      Select @address =  Ad1 from DDDG where id = @Iter;
  
      DECLARE @state nvarchar (100);
      SET @state = 'FL';
  
      DECLARE @zip nvarchar (100);
      SET @zip = '33142';
  
      DECLARE @city nvarchar (100);
      SET @city = 'Miami';
  
      DECLARE @nation nvarchar (2);
      SET @nation = 'us';
  
      DECLARE @g geography;
  
      WAITFOR DELAY '00:00:00.050'
      SET @g = dbo.Geocode(@nation, @state, @city, @zip, @address);
  
      Update DDDG 
      Set Lat = @g.Lat 
      where id = @Iter;
  
      Update DDDG 
      Set long = @g.Long 
      where id = @Iter;
  
       SET @Iter = @Iter + 1
  END
  
  DROP TABLE #Geo
  </code></pre>
  
  <p>Update .....</p>
  
  <p>thanks all... I almost got it working.. After a hell of a lot of trial and error I figured out that I needed to change the following </p>
  
  <pre><code>Update DDDG Set Lat = @g.Lat where id = id;
  Update DDDG Set long = @g.Long where id = id;
  </code></pre>
  
  <p>I only have one problem now. When I delete rows the RowNum and the @Iter no longer match up. is there a way of fixing this problem other than recreating the table every time? </p>