对表中的每一行运行SQL Server函数 [英] Run SQL Server function against each row in table

问题描述

我有一个函数，它在表中的for each row进行迭代。当它运行时，它应该查看每一行，拉入该行的相关SET值，并运行函数，该函数反过来返回一个结果并更新具有正确值的正确行。发生的是，它正在运行并返回最后一行的值，并更新所有具有该值的行。任何想法我做错了什么？

  SELECT 
 RowNum = ROW_NUMBER（）OVER（按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< = @MaxRownum 
 BEGIN 
 SELECT * 
 FROM #Geo 
 WHERE RowNum = @Iter 
 
 DECLARE @address nvarchar（100）; 
从DDDG中选择@address = Ad1，其中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）; 
 
更新DDDG 
设置Lat = @ g.Lat 
其中id = @Iter; 
 
更新DDDG 
设置long = @ g.Long 
其中id = @Iter; 
 
 SET @Iter = @Iter + 1 
 END 
 
 DROP TABLE #Geo 
  

更新.....

感谢所有...我几乎得到它的工作..很多尝试和错误我想出我需要改变以下

 更新DDDG设置Lat = @ g.Lat其中id = id; 
更新DDDG设置long = @ g.Long其中id = id; 
  

现在只有一个问题。当我删除行RowNum和@Iter不再匹配。有没有办法解决这个问题，而不是每次重新创建表？

解决方案

  WITH CTE_DDD（ID，Ad1）
 AS 
 （
 SELECT ID，Ad1 FROM DDD 
）
 UPDATE d 
 SET d.lat = dbo.Geocode（@nation，@state，@city，@ zip，Ad1）。 lat，long = dbo.Geocode（@nation，@state，@city，@ zip，Ad1）.long 
 FROM DDD d 
 WHERE d.id = ID 
  
 
 
 
修改查询从同行获得上述投票之后：
  WITH CTE_DDD AS 
（
 SELECT * FROM DDD 
）
 UPDATE CTE_DDD SET lat =（SELECT lat from dbo.Geocode（@nation，@state， @ city，@ zip，Ad）），long =（SELECT long from dbo.Geocode（@nation，@state，@city，@ zip，Ad））
  
我同意CROSS APPLY是一个更好的解决方案，因为它会每行调用一次函数。 
 
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? 
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 <= @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
Update .....

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 
Update DDDG Set Lat = @g.Lat where id = id;
Update DDDG Set long = @g.Long where id = id;
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? 
解决方案
WITH CTE_DDD(ID,Ad1)
AS
(
  SELECT ID, Ad1 FROM DDD
)
UPDATE d 
SET d.lat = dbo.Geocode(@nation, @state, @city, @zip,Ad1).lat, long = dbo.Geocode(@nation, @state, @city, @zip,Ad1).long 
FROM DDD d 
WHERE d.id = ID
Modified Query after getting downvotes from peers for the above one:
WITH CTE_DDD AS
(
  SELECT * FROM DDD
)
UPDATE CTE_DDD SET lat = (SELECT lat from dbo.Geocode(@nation, @state, @city, @zip,Ad)), long = (SELECT long from    dbo.Geocode(@nation, @state, @city, @zip,Ad))
I would agree that CROSS APPLY is a better solution as it would call the function once per row.   

                        
这篇关于对表中的每一行运行SQL Server函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！