Re-cursor - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Re-cursor

Author

Topic

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2004-11-12 : 05:10:48

Hi,
I have a table variable and then use a cursor for processing.
How can this cursor be optimized please?
P.S. @Deals is the table variable.
Thanks

---- Fill in the table variable
DECLARE ST_DEAL_SUMMARY_GET_Cursor CURSOR SCROLL LOCAL FOR SELECT
Deal_Id
FROM
@Deals Deal

order by Deal.SECURITY_NAME asc

OPEN ST_DEAL_SUMMARY_GET_Cursor

-- Go to the first record of page
FETCH ABSOLUTE @FirstPos FROM ST_DEAL_SUMMARY_GET_Cursor into @tempID

SET @RowCount=0
-- Insert each ID for page into temporary table
WHILE @@FETCH_STATUS=0 AND @RowCount<@PAGESIZE BEGIN
Insert Into @page_temp(tempID) VALUES (@tempID)
FETCH NEXT FROM ST_DEAL_SUMMARY_GET_Cursor into @tempID
SET @RowCount=@RowCount+1
END

-- Close and remove cursor
CLOSE ST_DEAL_SUMMARY_GET_Cursor
DEALLOCATE ST_DEAL_SUMMARY_GET_Cursor

Kristen
Test

22859 Posts

Posted - 2004-11-12 : 06:00:41

Repalce the cursor with (if I've got the code right!):

Insert Into @page_temp(tempID)
SELECT Deal_Id
FROM @Deals Deal
order by Deal.SECURITY_NAME asc

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-12 : 09:58:30

you might need a SET ROWCOUNT in there -- it looks like its only adding a certain number of rows, based on the @Page variable.

- Jeff

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2004-11-12 : 10:44:54

where do you set it please?
Thanks

Kristen
Test

22859 Posts

Posted - 2004-11-12 : 11:35:28

I can't see it Jeff. It's counting the number of rows, in @RowCount, but not doing anything with the count (or at least not withing the code snippet here)

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-12 : 16:04:02

quote:

WHILE @@FETCH_STATUS=0 AND @RowCount<@PAGESIZE BEGIN
Insert Into @page_temp(tempID) VALUES (@tempID)
FETCH NEXT FROM ST_DEAL_SUMMARY_GET_Cursor into @tempID
SET @RowCount=@RowCount+1

The above will loop until EOF or the @pagesize rows have been added.

fmardani, just add

SET ROWCOUNT @PageSize

before the INSERT statment. after the statement, add

SET ROWCOUNT 0

to set it back to normal. as always, read up on this in Books On-Line.

- Jeff

Kristen
Test

22859 Posts

Posted - 2004-11-13 : 01:29:14

"The above will loop until EOF or the @pagesize rows have been added."

Amazing! I stared at it until I was blue in the face trying to work out what @RowCount was doing!

Sorry about that.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources