Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Convert to CTE?

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 #deftable


Thanks.

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.
Go to Top of Page

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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-04 : 02:46:26
the possible solutions are

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/

Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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/
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -