SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Convert to CTE?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ssunny
Posting Yak Master

USA
133 Posts

Posted - 01/03/2013 :  13:14:48  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8764 Posts

Posted - 01/03/2013 :  13:27:56  Show Profile  Visit webfred's Homepage  Reply with Quote
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

USA
133 Posts

Posted - 01/03/2013 :  13:38:33  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 01/04/2013 :  02:46:26  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 01/04/2013 :  04:14:09  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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

USA
133 Posts

Posted - 01/04/2013 :  10:16:42  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 01/04/2013 :  10:32:02  Show Profile  Reply with Quote
Cool...post back on how you got on

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

ssunny
Posting Yak Master

USA
133 Posts

Posted - 01/10/2013 :  13:59:20  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 01/10/2013 :  22:54:01  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000