Aspnetpager + general paging Stored Procedure

Source: Internet
Author: User

Aspnetpager, used for paging page controls.

General paging stored procedures: Common paging methods include temporary table paging, select not int paging, select top, and rownumber (SQL server2005), which are unfamiliar with creating temporary table paging, the key is the use of the last three methods:

1. Using select not in paging is the lowest efficiency because you need to read the table multiple times.

2. The select top method is highly efficient, but the data identification fields may be discontinuous.

3. rownumber method: This method solves the problem of discontinuous fields in the top in method. It is applicable to general scenarios based on rownumber method. The combination of aspnetpager can effectively solve most paging problems.

Use testdb <br/> go <br/> If exists (Select name from sysobjects where name = 'page2005 'and type = 'P ') <br/> drop proc page2005 <br/> go <br/> Create proc page2005 <br/> @ pageindex int, <br/> @ pagesize int <br/> as <br/> declare @ recordcount int, @ pagecount int <br/> select @ recordcount = count (*) from student <br/> set @ pagecount = ceiling (@ recordcount * 1.0/@ pagesize) <br/> If @ pageindex> @ pagecount <br/> set @ pageindex = @ pagecount-1 -- last page <br/> If @ pageindex <0 <br/> set @ pageindex = 0 -- first page <br/> select sqno, * From <br/> (select Sid, deptid, SnO, sname, row_number () over (order by sid desc) as sqno from student) as S <br/> where S. sqno> (@ pageindex * @ pagesize) and S. sqno <= (@ pageindex + 1) * @ pagesize) <br/>

This is relatively simple, and it is still very good for beginners. The following stored procedure does not splice SQL statements in a complex way (because this may affect performance). Although it is small, it is fully-occupied, and has two functions: sorting and paging, which are versatile, compact and efficient structure. The Code is as follows:Create procedure [DBO]. [sp_super_page] </P> <p> @ tablename varchar (5000). -- the table to be paged can also be joined, such as DBO. employee or DBO. employee inner join on (DBO. employee. job_id = job_id) </P> <p> @ fields varchar (5000), -- fields in the table, which can be replaced by * </P> <p> @ orderfield varchar (5000 ), -- fields to be sorted </P> <p> @ sqlwhere varchar (5000), -- where clause </P> <p> @ pagesize int, -- page size </P> <p> @ pageindex int, -- index of the page to be displayed </P> <p> @ totalpage int output -- total number of pages </P> <p> as </P> <p> begin </ p> <p> Tran in TRAN </P> <p> declare @ SQL nvarchar (4000 ); </P> <p> declare @ totalrecord int; -- total number of records </P> <p> If (@ sqlwhere is null or @ sqlwhere = '') </P> <p> -- obtain the total number of records in the table without the WHERE clause </P> <p> set @ SQL = 'select @ totalrecord = count (*) from '+ @ tablename </P> <p> else </P> <p> -- Filter Using the WHERE clause </P> <p> set @ SQL = 'select @ totalrecord = count (*) from '+ @ tablename + 'where' + @ sqlwhere </P> <p> -- the total number of records obtained by executing SQL statements </P> <p> exec sp_executesql @ SQL, n'@ totalrecord int output', @ totalrecord output </P> <p> select @ totalpage = ceiling (@ totalrecord + 0.0)/@ pagesize) </P> <p> -- assign a unique row_number to the row based on the specific sorting field </P> <p> If (@ sqlwhere is null or @ sqlwhere = '') </P> <p> set @ SQL = 'select * from (select row_number () over (order by '+ @ orderfield +') as rowid, '+ @ fields + 'from' + @ tablename </P> <p> else </P> <p> set @ SQL = 'select * from (select row_number () over (order by '+ @ orderfield +') as rowid, '+ @ fields + 'from' + @ tablename + 'where' + @ sqlwhere </P> <p> -- make sure that the index of the current page is within a reasonable range </P> <p> If @ pageindex <= 0 </P> <p> set @ pageindex = 1 </P> <p> If @ pageindex> @ totalpage </P> <p>> set @ pageindex = @ totalpage </P> <p> -- obtain the exact row_number value of the current page in the entire result set </P> <p> declare @ startrecord int </P> <p> declare @ endrecord int </P> <p> set @ startrecord = (@ pageIndex-1) * @ pagesize + 1 </P> <p> set @ endrecord = @ startrecord + @ pagesize-1 </P> <p> -- output data on the current page </P> <p> set @ SQL = @ SQL + ') as t' + 'where rowid between' + convert (varchar (50), @ startrecord) + 'and' + convert (varchar (50), @ endrecord) </P> <p> exec (@ SQL) </P> <p> If @ error <> 0 </P> <p> begin </P> <p> rollback Tran </P> <p> return- 1 </P> <p> end </P> <p> else </P> <p> begin </P> <p> commit Tran </P> <p> return @ totalrecord </P> <p> end </P> <p> the pubs database is used for testing, </P> <p> use [pubs] </P> <p> go </P> <p> declare @ return_value int, </P> <p> @ totalpage int </P> <p> exec @ return_value = [DBO]. [sp_super_page] </P> <p> @ tablename = n' DBO. employee inner join on (DBO. employee. job_id = job_id) ', </P> <p> @ fields = n' EMP _ id, fname, minit, lname, jobs. job_id, job_lvl, pub_id, hire_date, job_desc, min_lvl, max_lvl ', </P> <p> @ orderfield = n' Max _ LVL, min_lvl DESC ', </P> <p> @ sqlwhere = NULL, </P> <p> @ pagesize = 10, </P> <p> @ pageindex = 3, </P> <p> @ totalpage = @ totalpage output </P> <p> select @ totalpage as n' @ totalpage' </P> <p> select 'Return Value '= @ return_value </P> <p> go </P> <p>

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