Sql Call Stored Procedure for Each Row Without Using a Cursor

Call procedure for each row without using cursors and loops?

I am not sure of your exact logic to split the string, but if possible you can make your split function an inline TVF (Heres one I made earlier):

CREATE FUNCTION dbo.Split(@StringToSplit NVARCHAR(MAX), @Delimiter NCHAR(1))
RETURNS TABLE
AS
RETURN
(   
    SELECT  Position = Number,
            Value = SUBSTRING(@StringToSplit, Number, CHARINDEX(@Delimiter, @StringToSplit + @Delimiter, Number) - Number)
    FROM    (   SELECT  TOP (LEN(@StringToSplit) + 1) Number = ROW_NUMBER() OVER(ORDER BY a.object_id)
                FROM    sys.all_objects a
            ) n
    WHERE   SUBSTRING(@Delimiter + @StringToSplit + @Delimiter, n.Number, 1) = @Delimiter
);

Then you can simply use this in your insert statement by using cross apply with the TVF:

DECLARE @T1 TABLE (ID INT IDENTITY, TextToSplit NVARCHAR(MAX) NOT NULL);
DECLARE @T2 TABLE (T1ID INT NOT NULL, Position INT NOT NULL, SplitText NVARCHAR(MAX) NOT NULL);

INSERT @T1 (TextToSplit) 
VALUES ('This is a test'), ('This is Another Test');

INSERT @T2 (T1ID, Position, SplitText)
SELECT  t1.ID, s.Position, s.Value
FROM    @T1 t1
        CROSS APPLY dbo.Split(t1.TextToSplit, N' ') s;

SELECT  *
FROM    @T2;

SQL - Call Stored Procedure for each record

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

SQL Server : call stored procedure with side effects for each row without using a cursor

cursors are inherently bad

No. Cursors are easily misused, and tend to be leapt upon by people new to SQL because they're from a procedural background and haven't even heard of "set-based". Cursors have their place, and if you've assessed the available approaches and concluded that cursors are a good fit, I'd say use one.

Using a WHILE loop to hide the fact that what you're really doing is using a cursor is also something I'd recommend against.

One final note - you mentioned fast_forward and read_only - but one other recommendation would be local - that way, if something goes wrong, at least the cursor is cleaned up when you exit whatever scope your cursor is running in - rather than persisting for the lifetime of the connection.

---

Related Topics