|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Procedure/cursor question about returning resultsraw 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 Murunion Tony Murnion (remove) wrote:
Show quote > I'm working on a procedure that needs to cycle through the records of some Your cursor wouldn't give predictable results anyway because you> 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 Murunion 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 Show quote "David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message news:1149798283.293874.324730@j55g2000cwa.googlegroups.com... > Tony 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 >> 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 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 > -- >  |
|||||||||||||||||||||||
Other interesting topics