Author |
Topic |
ssunny
Posting Yak Master
133 Posts |
Posted - 2013-01-03 : 13:14:48
|
Hello,Is it possible to rewrite following query using CTE or something better than a cursor?declare @tid int create table #deftable (tid int, ttext varchar (max)) 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 #deftableThanks. |
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2013-01-03 : 13:27:56
|
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... Too old to Rock'n'Roll too young to die. |
|
|
ssunny
Posting Yak Master
133 Posts |
Posted - 2013-01-03 : 13:38:33
|
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".Thank You. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-04 : 02:46:26
|
the possible solutions are1. 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 MVPhttp://visakhm.blogspot.com/ |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2013-01-04 : 04:14:09
|
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. |
|
|
ssunny
Posting Yak Master
133 Posts |
Posted - 2013-01-04 : 10:16:42
|
Thanks for the reply guys. I will see what I can do with this. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-04 : 10:32:02
|
Cool...post back on how you got on------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
ssunny
Posting Yak Master
133 Posts |
Posted - 2013-01-10 : 13:59:20
|
I tried all possible solutions but no luck as the store proc is using dynamic sql. Thanks all for your help/ |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-10 : 22:54:01
|
sorry whats the issue with dynamic sql? you can still make it work on set of values in dynamic sql------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|