How do I loop thru a result set?

[Andrew Young]

How do I loop thru a result set Without using a curosr?

[Mischa Sandberg]

[1]
select @key = min(key) from tab
while @key is not null begin
...
select @key = min(key) from tab where key > @key
end

[2]
Define a function which does your dirty work and returns a result value
(even dummy)
Then
select dbo.fn_dirtywork(col1,col2,col3,col4,...) from tab

[3]
Don't.

"Andrew Young" <at*******@yahoo.com> wrote in message
news:42**************************@posting.google.c om...

How do I loop thru a result set Without using a curosr?

[Hugo Kornelis]

On 28 Jun 2004 06:44:05 -0700, Andrew Young wrote:

How do I loop thru a result set Without using a curosr?

Hi Andrew,

Mischa already gave some suggestions. I'd have put number three first (and
repeated it several times).

Seriously: why do you want to loop through a result set? Can you describe
the business need you're trying to solve? Set-based solutions tend to be
better and quicker and most problems CAN be solved with set-based SQL.

On the other hand, if you really must (or want to) loop through a result
set, why not use a cursor? That's SQL's standard instrument for looping
through a result set. Of course, you can find other ways to do the same,
but they'll probably be less efficient than a cursor (which is already
very inefficient!)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Erland Sommarskog]

Andrew Young (at*******@yahoo.com) writes:

How do I loop thru a result set Without using a curosr?

I could a few more suggestions to Mischa's list, but I think I go with
his [3]. Then again, you should probably better clarify what your
real problem is. Maybe you are not iterating on the server, but in a
client?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Andrew Young]

Here is my process:

I am pulling performance metrics for over a thousand servers, W2K,
W2K3, NT, RS6000, and AS400. This equates to about 50 to 100 million
rows of data in a 24 hour period. I have a SLA of 24 hours to provide
this data on a daily basis.

My process consists of pulling a comma delimited format file that I
'bulk insert', from a UNC path, into a temporary table. I then fire a
stored proc that reads thru the temp table and process the data.

The data file format is:
hostname,application,application_instance,paramete r,timesatmp,data_point

My database consists of a hostname table, application table, instance
table, parameter table, agregatted data table and a raw data table.

All have primary key and constraints except for raw data table. My
first stored proc determines the existence of the hostname, app,
inst,param, and if they don't exist it adds them to the respected
table and then dumps the raw into the raw data table with application
and parameter keys to be used later. This proc will fire and process
several time in a minute processing inbound data files.

Every six hours a aggregation proc fires and move the data from the
raw table into the agg table and then deletes the raw stuff.

I hope this explains my need for looping. Any advise would be much
appreciated.

[Erland Sommarskog]

Andrew Young (at*******@yahoo.com) writes:

My process consists of pulling a comma delimited format file that I
'bulk insert', from a UNC path, into a temporary table. I then fire a
stored proc that reads thru the temp table and process the data.

The data file format is:
hostname,application,application_instance,paramete r,timesatmp,data_point

My database consists of a hostname table, application table, instance
table, parameter table, agregatted data table and a raw data table.

All have primary key and constraints except for raw data table. My
first stored proc determines the existence of the hostname, app,
inst,param, and if they don't exist it adds them to the respected
table and then dumps the raw into the raw data table with application
and parameter keys to be used later. This proc will fire and process
several time in a minute processing inbound data files.

Every six hours a aggregation proc fires and move the data from the
raw table into the agg table and then deletes the raw stuff.

I hope this explains my need for looping.

Not really. That is, I don't really see where the loop comes in.

When you get that data in, you would do

INSERT hostnames (hostname)
SELECT DISTINCT hostname
FROM rawdata r
WHERE NOT EXISTS (SELECT *
FROM hostname h
WHERE r.hostname = h.hostname)

and the similar for the other tables.

The aggregation should be possible to carry out with set-based statements.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp