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 2005 Forums
 Transact-SQL (2005)
 Can a CTE be used instead of a cursor/while loop?		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rudesyle
Posting Yak Master

110 Posts
Posted - 09/26/2007 :  10:47:01  Show Profile  Reply with Quote
I need to take a result set, and for each row, call a number of stored procedures. Can this be accomplished with a CTE instead? I'm thinking no....

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 09/26/2007 :  10:52:12  Show Profile  Visit SwePeso's Homepage  Reply with Quote
No. CTE still returns a resultset.
However, instead of using CURSOR you can use WHILE.


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 09/26/2007 :  10:53:13  Show Profile  Visit SwePeso's Homepage  Reply with Quote
The fastest way to do it, is to insert the resultset into a temporary table with an identity column.
Then do a WHILE for each record and call the SP.


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Kristen
Test

United Kingdom
22191 Posts
Posted - 09/26/2007 :  10:57:59  Show Profile  Reply with Quote
Or undocumented xp_execresultset ?
Go to Top of Page

rudesyle
Posting Yak Master

110 Posts
Posted - 09/26/2007 :  11:03:20  Show Profile  Reply with Quote
quote:
Originally posted by Peso

The fastest way to do it, is to insert the resultset into a temporary table with an identity column.
Then do a WHILE for each record and call the SP.


E 12°55'05.25"
N 56°04'39.16"


Yeah, I know. However, you'd probably want to use a table variable instead of a temp table.
Go to Top of Page

Van
Constraint Violating Yak Guru

456 Posts
Posted - 09/26/2007 :  11:04:56  Show Profile  Reply with Quote
Depends on the table...
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.05 seconds. Powered By: Snitz Forums 2000