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
Sql - Select Parent and Child Records in an Order
How to Search Json Array in MySQL
Combining (Concatenating) Date and Time into a Datetime
Oracle SQL Hours Difference Between Dates in Hh:Mm:Ss
Convert Varchar Mmddyyyy to Mm/Dd/Yyyy Datetime and Select the Most Recent Date Only
Mysql Select Rows on First Occurrence of Each Unique Value
Sql Select Everything After a Certain Character
Addition of Total Hours in SQL Server
Sql - Sum of Positive and Negative Numbers Using Subquery
How to Merge Rows on Specific Condition
Checking a Column If It Contains a Row Value
Sql: How to Get Both Match and Non-Match Records
Extracting Data Between Two Delimiters in SQL Server
Postgresql Convert Columns to Rows Transpose
Sql Method to Replace Repeating Blanks With Single Blanks
Get the Number of Digits After the Decimal Point of a Float (With or Without Decimal Part)