Aspnetpager + general paging Stored Procedure

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 dbo.jobs on (DBO. employee. job_id = dbo.jobs. 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 dbo.jobs on (DBO. employee. job_id = dbo.jobs. 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>