declare tcursor cursor for select t_id from test open tcursor fetch tcursor into @tid while @@fetch_status = 0 begin insert into #deftable (tid, ttext)
exec ('dbo.some_store_proc ' + @tid )
fetch tcursor into @tid end close tcursor deallocate tcursor select * from #deftable
The question is: What is your "dbo.some_store_proc" performing with one tid at a time? Maybe the solution is to make it performing for many tids at a time. But we can't see the code...
Thanks for the reply webfred. "dbo.some_store_proc" has very long,recursive code and I am not familiar with it. I was hoping to change cursor and temp table to something else without worrying about "dbo.some_store_proc".
1. make sp accept array of values as Fred suggested and do processing as a batch. 2. Make procedure into UDF so that you can call it set based using APPLY operator. But code should not have dynamic sql, non determinitic functions etc
------------------------------------------------------------------------------------------------------ SQL Server MVP http://visakhm.blogspot.com/
You can populate a temp table with the values and loop through it but you won't get any advantage over using the cursor.
The only way to may any fundamental changes to this would be to look at the SP and transfer the code in-line - or change the SP to access the temp table and put the loop in there which is in effect the same thing. You will probably find that you can preprocess a lot of data using set based operations - and probably eventually get rid of the SP.
If you don't want to change the SP why do you want to change the controlling process? The SP takes an ID and you have to call it for each one so there's not a lot you can do without changing the SP.
========================================== Cursors are useful if you don't know sql. SSIS can be used in a similar way. Beer is not cold and it isn't fizzy.
Convert to CTE?
Posted - 01/03/2013 : 13:14:48
