SQL - Call Stored Procedure for each record
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?
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
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