Calling a stored procedure within a cursor loop
-
I have a stored procedure that opens up a cursor and while looping through it, calls another stored procedure. However, I can only get the second one called to loop once.
Is there any way, or special command, to get it to do what I want (execute for every record in the cursor on a record by record basis)?
CREATE PROCEDURE sp_build_comprehensive_pledge_detail_table AS
Set NOCOUNT ON
Declare @camp int, @comp int, @master int,@fed int,@year char(4)
Declare rs_curs cursor for
select distinct campaignyear, campaignaccountnumber, masteraccount, federationaccount,[company account]
from vw_summary_parent
GROUP BY campaignyear, campaignaccountnumber, masteraccount, federationaccount,[company account]
ORDER BY campaignyear, campaignaccountnumber, masteraccount, federationaccount,[company account]
open rs_curs
fetch next from rs_curs into @year, @camp, @master, @fed, @comp
exec sp_comprehensive_pledge_detail_company_master_out @camp, @comp, @master,@fed,@year
/*select @year,@camp,@master,@comp*/
While (@@fetch_status=0)
begin
fetch next from rs_curs into @year, @camp, @master, @fed, @comp
exec sp_comprehensive_pledge_detail_company_master_out @camp, @comp, @master,@fed, @year
/*Print 'building data into table'*/
/*select @year,@camp,@master,@comp*/
end
close rs_curs
deallocate rs_curs
Print 'finished job'
Set NOCOUNT OFF
-
If you need to do row processing a cursor will work, or you can right a "fake" one using select inside a while loop. I'd suggest backing up and having it just print the pkey or something for each row so you can make sure your cursor is sound, then if nothing changes try building up a string equivalent to what you'll pass to the proc and print that, then finally try calling the proc itself. Just good slow debugging should get you there.
Andy
-
have you checked for recursive triggers in the second procedure!!?
-
Thank you, everyone, I came up with a set level solution for the whole piece
-
Again, too late for a solution, however the cause of your problem could be found in the fact that the @@fetch_status is not checked after the FETCH statement.
From BOL :
"Because @@FETCH_STATUS is global to all cursors on a connection, use @@FETCH_STATUS carefully. After a FETCH statement is executed, the test for @@FETCH_STATUS must occur before any other FETCH statement is executed against another cursor."
So my guess is that in your nested stored procedure, you also issue a FETCH statement somehow, resetting the @@FETCH_STATUS value in your outer loop.
The solution is to write your cursor loop like :
FETCH NEXT FROM cursor INTO ...
WHILE (@@FETCH_STATUS=0)
BEGIN
EXEC your_procedure
FETCH NEXT FROM cursor INTO ...
END
I know its of no use anymore, but it might come in handy in the future.
And of course, a set based approach is much better !!
-
could someone please elaborate on what a "Set based approach" would be?
I have a stored proc that uses a similar algorithm to this one ...i.e. loops through a recordset and executes a stored proc on each record.
It works well for me... it is much faster than my original algorithm, which retrieved a recordset from asp and then in asp looped through it executing a stored proc on each record.
thanks,
Guy
-
What is done in the 'cursor loop' approach, is getting a recordset and stepping through the recordset, one record at a time.
In general this is discouraged because it eats up a lot of (valuable) resources (memory, diskspace and CPU).
With a SET based approach, you try to eliminate the cursorloop and achieve the desired result using one (or a few) simple queries.
In many cases, a cursor loop is the easiest solution to master, especially for programmers since they are used to the simple flow concepts like WHILE and IF.
The set based approach is more difficult (that's my experience), since you have to look at the problem from a completely different angle.
The simplest example would be if you have a cursor to loop through a table, updating a field to a new value for each record.
A SET based approach would be to just use the 'UPDATE' SQL syntax.
-
One other consideration I've found useful in deciding on choosing the approach is to think about maintenance. I have seen, and have been guilty of, creating fairly complex SQL structures to achieve a result in a Set Based manner as opposed to a proceduraly more simple Cursor method. Now I realise not everyone is as clever and competent as me..... Sometimes the Cursor approach though definetely not as efficient, is nevertheless simpler and clearer for someone else to follow.
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961 -
You're completely right on this. As always, there is no single 'best' solution.
In this case there's a trade off between simplicity/mantainability and perfomance.
My personal experience is that in general performance is the winner here... Documentation of the set based approach is a must in most cases.
-
Thank you to everyone. I actually solved this a while ago with a set level solution. However, problems with the database are causing me to have to parse through that solution piece by piece. The company who built this crappy application have been making numerous changes.
-
Interesting....
I understand that cursors are quite resource intensive, and avoid them when possible; however it's nice to have as a backstop.
I'm not above learning something new, so I thought I'd pose a simple SQL statement that I recently created using cursors.
Essentially there is one table consisting of thousands of transactions. I want the top 10 transactions for each customer - I'm using a cursor for this - how would it be done in a set approach?
-
Brendon, for set based solutions this way will usually work.
SELECT ColListHere
FROM tblTransactions oq
WHERE oq.TransID IN (
SELECT TOP 10 iq.TransID
FROM tblTransactions iq
WHERE iq.CustID = oq.CustID)
Hope this helps.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
crosspatch,
I can't agree more. For a couple of years I avoided cursors out of been told they're 'bad form' and inefficient. I'd always found a set based approach - no matter how complicated (lots of sub queries within sub queries). In fact it became more based on a challenge and less on the most logical approach - and could be an absolute night mare to maintain when I came back to it a week or two later. Recently, I was presented with a problem that could not be solved by the set approach and so I had to use my first cursor (actually cursor within a cursor). And now that I've finally succumbed I can use them as i need.
It's most liberating.
-
I personally try to find a set based or cursor solution only when performance will show a gain with enough impact to warrant. Yes sometimes set based solutions can be a nightmare but them may make the difference between a 52 min query and a 30 sec query (actually had this one happen with a cursor taking forever, I took the developers code and rewrote as a nasty looking set based query which showed as I state a major improvement). I have also on a few occasions found while loop solutions and fewer cursor solutions that work (conditions apply here that may be specific to me), replacing set based solutions. Also, the larger datasets the higher tendency that cursor solutions will provide a better result of performance (Oracle DB's show these the greatest, so some weight is on the engine itself as well). Finally, many factors can affect performance and which solution is best, just keep an open mind and make sure no matter what solution you take when you can experiment with alternate solutions and variations thereof.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
i'm writing a similar script. get a list of tables from sysdatabases and run a script on each of them. i've used a cursor to get the name into @DB, then
EXECUTE('USE '+ @DB ) followed by some tsql.
but it doesnt work. i cant get it to run the use statement at all. the cursor is working perfect, execute also works on simple select statements.
I'm on a maintenance project and so can;t create any objects on the target db, so no stored procedures.
any ideas?
manish
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply