Introduction to the use of SQL Server two paging stored procedures _mssql

Because now many of the corporate recruitment of the written test will let the recruitment of write a paging stored procedures, some enterprises even require candidates to implement pagination in two ways, if not in the actual project to use excessive page, so many candidates will have a certain problem, the following introduction to two ways of pagination.

One, take the student table as an example, in the database has a student table, the field has Studentno,, Loginpwd, Studentname,sex,classid,phone,address,borndate,email,isdel

Request: Inquiry Student's information, each page displays 5 records

Second, the first way paging: the use of subqueries not in

For example:

First page

Select Top 5 * from Student

Page Two: The first 10 in the query is not in the first 5 records, then is 6-10, that is, the second page

Select Top 5 * "from Student where Studentno is not in" (select Top Studentno from Student)

In the same vein, you can get the third page 、、、、、、、

This way I believe that we all can understand that this kind of paging stored procedure writing is not to do more introduction, focusing on the following kind of paging method.

Third, the second way paging: the use of row_number () this self-contained function

Since after 05, provides a function specifically for paging, that is, the row_number () function, the basic syntax for pagination: Row_number () Over (sort field): You can sort by a specified field and add an unbroken line number to each row of the result set after the sort. Equal to the continuous ID value,

For example, the SQL statement: Select Row_number () Studentno ID, * from Student so the result set can be seen:

So we can see that the ID values are sequential and all the next stored procedures are simpler to write.

Note: We must have a new name for this result, such as we named temp, so the paging stored procedure can write:

if exists (select * from sysobjects where name= ' usp_getpagedata ')
drop proc Usp_getpagedata-if there is a name usp_ Getpagedata stored Procedures Delete Go create
proc Usp_getpagedata--Create name Usp_getpagedata stored procedure
@toPage int=0 output, total pages
@pageIndex int = 1--The default display of the first page
@pageCount int = 5--The default record for each page is 5 as
Select Temp. Studentno,temp. Loginpwd,temp. Studentname,temp. Sex,temp. Classid,temp. Phone,temp. Address,temp. Borndate,temp. Email,temp.isdel from
(select Row_number ()-Studentno) id,* from Student) temp
where id> (@pageI NDEX-1) * @pageCount and id<= @pageIndex * @pageCount

set @toPage =ceiling (select COUNT (*) from Student) *1.0/@ PageCount)--Use the ceiling function to calculate the total number of pages go

Description because in the actual project development, often want to show the total number of pages to the user to see, all stored procedures here add a topage parameter, because it is to output to the user, all parameter types are defined as output, and set for the assignment.

The above is a two-page approach to the introduction, if you have any questions or do not understand can leave a message to me.