Cursors for paging stored procedures

Source: Internet
Author: User

1. Personally, I think the best way to do this is to:


Select Top * from table where id>200
Written as stored procedures, the above statement to spell the SQL statement, to get the last greater than which ID number


2. A table with a cursor that fits only a small amount of data, if the table is above 10,000 lines, it sucks.

Your stored procedures are not comparable to the not in pagination, example:


Select Top * from Customers WHERE Customerid isn't in (select top Customerid from Customers)
DECLARE @SQLStr varchar (8000)
Set @SQLStr = ' Select top ' +cast (@ per page size as varchar) + ' * FROM table WHERE primary key column not in (SELECT top ' +cast (@ per page size *@ page as varchar) + ' main Key columns from table) '
EXEC (@SQLStr)



3. Cursors are stored in memory and are very time-consuming.

Once the cursor is established, the associated record is locked until the cursor is canceled

Cursors provide a step-by-step scan of a particular set, typically using cursors to traverse data line by row, and to perform different operations depending on how the data is removed. For multiple tables and large tables, the cursor (large data set) loop is easy to get the program into a long wait or even crash.

So, my personal experience, is the 10,000 up on the table, without cursors. A table with a small amount of data, which can be used with cursors when appropriate

Because of cursors, it's a good way to traverse a set of small data volumes.

4. customizing pagination with temporary tables and using cursors to customize pagination are not good!

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.