Procedure/cursor question about returning results

I'm working on a procedure that needs to cycle through the records of some
raw data and combine the the current record with the datetime field of the
prior record. I have been able to write a script to do that with cursors
and variables but my problem is it returns each record separately. How do I
go about getting the procedure to return all the records as one set of data?
To see what I mean, the following script for the Pubs DB returns each pass
through the data as a seperate query. Since I can't do a select *, what
approach should I take? If you want the actual script I have so far, I
would be happy to provide it.
DECLARE authors_cursor CURSOR
FOR SELECT * FROM authors
OPEN authors_cursor
FETCH NEXT FROM authors_cursor
WHILE @.@.FETCH_STATUS = 0
begin
FETCH NEXT FROM authors_cursor
end
Close Authors_cursor
deallocate authors_cursor
Thanks in advance
Tony MurunionTony Murnion (remove) wrote:
> I'm working on a procedure that needs to cycle through the records of some
> raw data and combine the the current record with the datetime field of the
> prior record. I have been able to write a script to do that with cursors
> and variables but my problem is it returns each record separately. How do
I
> go about getting the procedure to return all the records as one set of dat
a?
> To see what I mean, the following script for the Pubs DB returns each pass
> through the data as a seperate query. Since I can't do a select *, what
> approach should I take? If you want the actual script I have so far, I
> would be happy to provide it.
> DECLARE authors_cursor CURSOR
> FOR SELECT * FROM authors
> OPEN authors_cursor
> FETCH NEXT FROM authors_cursor
> WHILE @.@.FETCH_STATUS = 0
> begin
> FETCH NEXT FROM authors_cursor
> end
> Close Authors_cursor
> deallocate authors_cursor
> Thanks in advance
> Tony Murunion
Your cursor wouldn't give predictable results anyway because you
haven't specified ORDER BY.
Cursors are rarely a good way to get results out of data. In this case
you can possibly use a query. To take another example from Pubs:
SELECT T1.title_id, T1.title,
T1.pubdate AS current_pubdate,
MAX(T2.pubdate) AS previous_pubdate
FROM titles AS T1
LEFT JOIN titles AS T2
ON T1.pubdate > T2.pubdate
GROUP BY T1.title, T1.title_id, T1.pubdate
ORDER BY current_pubdate, previous_pubdate ;
To do that with a cursor you could insert each row to a table variable
and then SELECT from the variable. Don't forget ORDER BY though!
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks for the reply. I just started looking into the tables option. I'm
fairly new to advanced script writing. I've done a lot of select and
updates over the years but most of my data manipulation\retrieval has been
through Crystal. In this current project, I need to do the manipulaton
before the ending result.
With my cursor testing, I do have the order by clause for just the reasons
you stated. The results I'm getting are valid - I just need them to come
over as one record set. The tables option may do that for me. I was also
just looking at the user definde functions. Since this database I am reading
will be generating a few thousand records a day, what do you think will
ultimately give me the best performance? The join example you gave seems
like it would bog down with larger volumes of data.
I did not mention before but this is on sql 2000
Thanks again.
Tony
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1149798283.293874.324730@.j55g2000cwa.googlegroups.com...
> Tony Murnion (remove) wrote:
> Your cursor wouldn't give predictable results anyway because you
> haven't specified ORDER BY.
> Cursors are rarely a good way to get results out of data. In this case
> you can possibly use a query. To take another example from Pubs:
> SELECT T1.title_id, T1.title,
> T1.pubdate AS current_pubdate,
> MAX(T2.pubdate) AS previous_pubdate
> FROM titles AS T1
> LEFT JOIN titles AS T2
> ON T1.pubdate > T2.pubdate
> GROUP BY T1.title, T1.title_id, T1.pubdate
> ORDER BY current_pubdate, previous_pubdate ;
> To do that with a cursor you could insert each row to a table variable
> and then SELECT from the variable. Don't forget ORDER BY though!
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>