SQL - 为每条记录调用存储过程

问题描述

我正在寻找一种为 select 语句的每条记录调用存储过程的方法.

I am looking for a way to call a stored procedure for each record of a select statement.

SELECT @SomeIds = (
    SELECT spro.Id
    FROM SomeTable as spro
    INNER JOIN [Address] addr ON addr.Id = spro.Id
    INNER JOIN City cty ON cty.CityId = addr.CityId
    WHERE cty.CityId = @CityId
)


WHILE @SomeIds  IS NOT NULL
BEGIN
    EXEC UpdateComputedFullText @SomeIds
END

上面这样的事情当然行不通，但是有没有办法做到这样的事情?

Such a thing above is not working of course, but is there a way to do something like that?

推荐答案

为此您需要使用游标.

DECLARE @oneid int -- or the appropriate type

DECLARE the_cursor CURSOR FAST_FORWARD
FOR SELECT spro.Id
    FROM SomeTable as spro
        INNER JOIN [Address] addr ON addr.Id = spro.Id
        INNER JOIN City cty ON cty.CityId = addr.CityId
    WHERE cty.CityId = @CityId

OPEN the_cursor
FETCH NEXT FROM the_cursor INTO @oneid

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC UpdateComputedFullText @oneid

    FETCH NEXT FROM the_cursor INTO @oneid
END

CLOSE the_cursor
DEALLOCATE the_cursor

这篇关于SQL - 为每条记录调用存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！