Procedure/cursor question about returning results (started by Tony Murunion)

microsoft.public.sqlserver.programming

9 Jun 2006 8:10 PM

John the ScrubMaster

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 manipulation
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 defined 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_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 current record with the date/time 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 separate 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.

Show quote

>>
>> 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
> --
>

It sounds like you want to process the entire record set, not processing
record by record. Have you considered SELECT INTO?

Defining a cursor and using FETCH by definition will only process row by
row. SELECT INTO will be a hundred times more efficient. Only occasionally
will you need to define a cursor and FETCH individual records.

--Cheers

----John