My Cursor Operation - Efficient?

17 Dec 2005 9:51 AM

Simon Harris

Hi All,

I am trying to write a query that will conditionally carry out some string
manipulation on as the records are retrieved. This much I am fine with,
what
I'm not sure about is the most efficient way to use a cursor.

Below is what I have written so far (psudo code for ease of reading) which
in query analyzer seems to return a result set per record that I request
when getting data from the cursor, I am wondering if there might be a
better
way which could some how return all records in one result set?

Thank you.

Simon.

-- Get source data into a cursor
DECLARE ProdCursor CURSOR FOR
SELECT ColA, ColB, ColC, ColD FROM aTable
OPEN ProdCursor
FETCH NEXT FROM ProdCursor INTO @ColA, @ColB, @ColC

-- Loop data
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @ColA
FETCH NEXT FROM ProdCursor INTO @ColA, @ColB, @ColC
END

-- Clean up
CLOSE ProdCursor
DEALLOCATE ProdCursor

----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 2987 spam emails to date.
Paying users do not have this message in their emails.
Try www.SPAMfighter.com for free now!

17 Dec 2005 10:08 AM

David Portas

Simon Harris wrote:
Show quote

> Hi All,
>
> I am trying to write a query that will conditionally carry out some string
> manipulation on as the records are retrieved. This much I am fine with,
> what
> I'm not sure about is the most efficient way to use a cursor.
>
> Below is what I have written so far (psudo code for ease of reading) which
> in query analyzer seems to return a result set per record that I request
> when getting data from the cursor, I am wondering if there might be a
> better
> way which could some how return all records in one result set?
>
> Thank you.
>
> Simon.
>
> -- Get source data into a cursor
> DECLARE ProdCursor CURSOR FOR
> SELECT ColA, ColB, ColC, ColD FROM aTable
> OPEN ProdCursor
> FETCH NEXT FROM ProdCursor INTO @ColA, @ColB, @ColC
>
> -- Loop data
> WHILE @@FETCH_STATUS = 0
> BEGIN
> SELECT @ColA
> FETCH NEXT FROM ProdCursor INTO @ColA, @ColB, @ColC
> END
>
> -- Clean up
> CLOSE ProdCursor
> DEALLOCATE ProdCursor
>
>
>
> ----------------------------------------
> I am using the free version of SPAMfighter for private users.
> It has removed 2987 spam emails to date.
> Paying users do not have this message in their emails.
> Try www.SPAMfighter.com for free now!

99.999% of the time cursors are not the best way to manipulate data.
Always look for a set based solution first. Unfortunately, you've told
us virtually nothing about what you want to do so it's hard to help
you. Based on what you've given, you could just do:

SELECT cola
FROM aTable ;

and then put whatever string manipulation is required in the SELECT
list.

Please give us a better spec if you need more help. See:
http://www.aspfaq.com/etiquette.asp?id=5006

--
David Portas
SQL Server MVP
--