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


Because many written recruitment tests now let the recruiters write a paging stored procedure, some enterprises even require the candidates to realize paging in two ways. If paging has not been used in the actual project, many candidates will have certain problems. The following two paging methods are introduced.

1、 Take the student table as an example. There is a student table in the database. The fields include studentno, loginpwd, studentname, sex, CLassID, phone, address, borndate, email and isdel

Requirements: query student information and display 5 records per page

2、 The first method is paging: use sub query not in

For example:

first page

select top 5 * from Student

Page 2: query the first 10 records that are not in the first 5 records, then it is 6-10, that is, page 2

select top 5 * from Student where StudentNo not in(select top 10 Studentno from Student)

Similarly, you can get the third page

I believe everyone can understand this method. I won’t introduce this paging stored procedure writing method more. I will focus on the following paging method.

3、 The second way is paging: using row_ Number () is a built-in function

Since 05, a function dedicated to paging is provided, that is row_ Number() function, the basic syntax of paging: row_ Number () over: you can sort according to the specified field, and add an uninterrupted line number to each row of the sorted result set, which is equivalent to continuous ID values,

For example, the SQL statement: select row_ Number () over (order by studentno) id, * from student, the result set can be seen as follows:

Then we can see that the ID value is continuous, and all subsequent stored procedures are relatively simple to write.

Note: we must name the result set a new name. For example, we name it temp, so the paging stored procedure can write:

if exists( select * from sysobjects where name='usp_getPageData')
drop proc usp_ Getpagedata -- if any, the name is USP_ The stored procedure of getpagedata is deleted
create proc usp_ Getpagedata -- create name USP_ Getpagedata stored procedure
@Topage int = 0 output, -- total pages
@PageIndex int = 1, -- the first page is displayed by default
@Pagecount int = 5 -- 5 records per page by default
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() over (Order by studentno) id,* from Student) temp
where id>(@pageIndex-1)*@pageCount and id<[email protected]*@pageCount

Set @ topage = ceiling ((select count (*) from student) * 1.0 / @ pagecount) -- use the ceiling function to calculate the total number of pages

Note: in the actual project development, the total number of pages is often displayed to the user. A topage parameter is added to all stored procedures here. Because it is to be output to the user, all parameter types are defined as output and assigned with set.

The above is an introduction to the two paging methods. If you have any questions or don’t understand, you can leave a message to me.

Recommended Today

Swift advanced (XV) extension

The extension in swift is somewhat similar to the category in OC Extension can beenumeration、structural morphology、class、agreementAdd new features□ you can add methods, calculation attributes, subscripts, (convenient) initializers, nested types, protocols, etc What extensions can’t do:□ original functions cannot be overwritten□ you cannot add storage attributes or add attribute observers to existing attributes□ cannot add parent […]