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

Question

Before you say this is identical to SQL Call Stored Procedure for each Row without using a cursor let me clarify my question:

The stored procedure does have side effects. In fact, it's all about those side effects, as the results of each call influence further processing.

Think of it like this: the table contains rule definitions, and the proc executes those rules literally RBAR, making changes to another table.

Under those conditions I do not see how a set operation is possible, CROSS APPLY probably can't be used because of side effects, but it's also not needed, since I'm not really joining the rule table with any results.

If the solution is really RBAR, should I still try to avoid using CURSOR? Is using WHILE with READ TOP 1 WHERE Key > @Key a better solution? Why?

The more I searched, the more I came to the conclusion that fast_forward read_only cursor is the simplest and fastest solution.

Answer 1

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.

Answer 2

        --- create one temp table using temprory talbe
        declare @test_Table  table (id int, num int, name varchar(50))
        --- fetch all data from your table to temp talbe
        insert into @test_Table (id,num,name)
        select id,number,name from TESTNUMBERS

        ----select * from TESTNUMBERS

        declare @count int
        declare @id int
        set @count=(select COUNT(1) from @test_Table)
        --- create while loop to work on particular row 
        while (@count <>0)
        begin
        set id=(select top 1 id from @test_Table)
        ---- do you task wiht that row and call your stored procedure on this row
        ---then delete that row form temp table variable 
        delete from @test_Table where id=@id
        --- decarease count to maintain loop 
        set @count=@count-1
        end

you can use this type of loop to work on each row without using cursor.

steps :

1. stoere data in temprory teble

2. get count of all rows

3. in loop fetch top 1 row from temp table

4. do all your task in on that row

5. den delete that row from temp table

6. decrement count by 1

6 if it helps then enjoy.....