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.

A) which database are you using? B) what's wrong with using cursors? Every database select query uses one. — Bohemian, May 21 '13 at 02:07
A) MS SQL Server 2012 B) It is said that cursors are inherently bad. SQL queries do use cursors, but they: 1) look simple and clean. 2) don't allow anything to execute in the middle of it, and 3) guarantee that the cursor is closed at the end. If nothing else, it's a bad coding style to use cursors when alternative solutions are available. — MOHCTP, May 21 '13 at 02:29
Welcome to stored procedure land. Perhaps you should [reconsider using stored procedures](http://stackoverflow.com/questions/8516088/when-to-use-a-stored-procedure-mysql/8516334#8516334) — Bohemian, May 21 '13 at 03:26
@Bohemian I can address every one of those points to justify my use of a stored procedure here, but I will give just the main one: our operations dashboard of views and alerts is basically a database exposed to queries. SQL is **the only choice** Other reasons, like, non-portability, simply do not apply, since we will never use a version of SQL other than MS SQL :-) — MOHCTP, May 21 '13 at 04:03
What connects the webpage to the database... there must be some kind of application server where you could turn queried data into displayed data — Bohemian, May 21 '13 at 09:49
The application server is just a load balancer that allows you to run SQL code on one of the DB servers. You can execute ad-hoc code or run a stored procedure, but the application server doesn't manipulate the data, it's too generic to have that kind of logic, and needs to be very lightweight, because it executes thousands of queries per second. — MOHCTP, May 22 '13 at 01:50

score 2 · Accepted Answer · answered May 21 '13 at 06:26

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.

score 0 · Answer 2 · answered May 21 '13 at 05:23

        --- 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 :

stoere data in temprory teble
get count of all rows
in loop fetch top 1 row from temp table
do all your task in on that row
den delete that row from temp table
decrement count by 1

6 if it helps then enjoy.....

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

2 Answers2