SQL - Call Stored Procedure for each record

Question

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?

Answer 1

Put the Ids into a Temporary table variable, and then iterate throught each row: (You do not need to use a cursor which will be considerably slower)

   Declare @Keys Table (key integer Primary Key Not Null)    Insert @Keys(key)    SELECT spro.Id      FROM SomeTable as spro         JOIN [Address] addr ON addr.Id = spro.Id          JOIN City cty ON cty.CityId = addr.CityId     WHERE cty.CityId = @CityId    -- -------------------------------------------    Declare @Key Integer    While Exists (Select * From @Keys)      Begin          Select @Key = Max(Key) From @Keys          EXEC UpdateComputedFullText @Key          Delete @Keys Where Key = @Key      End  

EDIT Delete is not slow when used with a filter predicate driven against a very narrow unique index, as this is. But it can easily be avoided, just by making loop as follows:

Declare @Key Integer = 0 While Exists (Select * From @Keys               Where key > @Key)  Begin      Select @Key = Min(Key) From @Keys                    Where key > @Key      EXEC UpdateComputedFullText @Key      -- Delete @Keys Where Key = @Key No Longer necessary   End     

Answer 2

You need to use a cursor for 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