FOR SELECT

In another database that I've used, I found the FOR SELECT syntax
handy. It allows a stored procedure to perform actions for each
record returned by the select statement. I can't find the
documentation on this in SQL Server. Am I missing something here?
I've just started with this product, so it won't surprise me to find
that it's right in front of my face, so to speak or that another
syntax performs the same function.

Thanks for any help with this.Weaver (we.aver@.verizon.net) writes:
> In another database that I've used, I found the FOR SELECT syntax
> handy. It allows a stored procedure to perform actions for each
> record returned by the select statement. I can't find the
> documentation on this in SQL Server. Am I missing something here?
> I've just started with this product, so it won't surprise me to find
> that it's right in front of my face, so to speak or that another
> syntax performs the same function.

Well, there is:

DECLARE mycur CURSOR FOR
SELECT Orders FROM Orders WHERE ...

So it is not a stored procedure, but just a bunch of statments.

However, cursors and other iterative solutions are in very most cases
inferior in perfomance than set-based statements, so you should avoid
them.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> However, cursors and other iterative solutions are in very most cases
> inferior in perfomance than set-based statements, so you should avoid
> them.

Thanks for the info. Pretty much like the FOR syntax.

What degree of performance hit are we talking about here? Have you
seen any stats on this?|||Weaver (we.aver@.verizon.net) writes:
> What degree of performance hit are we talking about here? Have you
> seen any stats on this?

Depends on the number of rows involved. For a handful it may not be
noticeable. If you have ten thousand rows, an iterative solution may
take 30 seconds when a set-based runs subsecond.

With other words: the performance hit can be huge.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I did some experiments a while back, using 2 10,000 record lookups
into a 1million record dataset. Teh set based solution took a while,
several minutes. A semi set semi cursor solution took over half an
hour. The full nested cursor solution - well I killed it after
1/2hour. It had got less than 1% through the dataset...
draw your own conclusions :)

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns94F55EB3E8181Yazorman@.127.0.0.1>...
> Weaver (we.aver@.verizon.net) writes:
> > What degree of performance hit are we talking about here? Have you
> > seen any stats on this?
> Depends on the number of rows involved. For a handful it may not be
> noticeable. If you have ten thousand rows, an iterative solution may
> take 30 seconds when a set-based runs subsecond.
> With other words: the performance hit can be huge.|||> With other words: the performance hit can be huge.

OK! I'll run some tests on this before I run off and design something around it.

Thanks again for the help.|||WangKhar wrote:
> I did some experiments a while back, using 2 10,000 record lookups
> into a 1million record dataset. Teh set based solution took a while,
> several minutes. A semi set semi cursor solution took over half an
> hour. The full nested cursor solution - well I killed it after
> 1/2hour. It had got less than 1% through the dataset...
> draw your own conclusions :)

I'm sure the set-based version was fastest, but it may not be by
as much as it seems. The indexing will make a lot less difference
on the set-based version, because you're not faced with doing a
table scan (or a filter, if the index doesn't narrow it down much)
for every single row.

Bill|||It was properly indexed...

William Cleveland <WCleveland@.Ameritech.Net> wrote in message news:<9zxtc.22686$qt1.14178@.newssvr31.news.prodigy.com>...
> WangKhar wrote:
> > I did some experiments a while back, using 2 10,000 record lookups
> > into a 1million record dataset. Teh set based solution took a while,
> > several minutes. A semi set semi cursor solution took over half an
> > hour. The full nested cursor solution - well I killed it after
> > 1/2hour. It had got less than 1% through the dataset...
> > draw your own conclusions :)
> I'm sure the set-based version was fastest, but it may not be by
> as much as it seems. The indexing will make a lot less difference
> on the set-based version, because you're not faced with doing a
> table scan (or a filter, if the index doesn't narrow it down much)
> for every single row.
> Bill