Shulou(Shulou.com)05/31 Report--

This article shows you how to use paging stored procedures in SQL Server. The content is concise and easy to understand, which will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

1. Take the student table as an example. There is a Student table in the database with fields such as StudentNo, LoginPwd and StudentName,Sex,ClassId,Phone,Address,BornDate,Email,isDel.

Requirements: query students' information, display 5 records per page

Second, the first way of paging: using subquery not in

For example:

Page one

Select top 5 * from Student

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

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

In the same way, you can get page 3,

In this way, I believe everyone can understand that this paging stored procedure writing method does not introduce much, focusing on the following paging method.

Third, the second way of paging: using ROW_NUMBER (), which is a built-in function

Since 2005, a special function for paging has been provided, and that is the function ROW_NUMBER (). The basic syntax of paging: ROW_NUMBER () over (sort field): it can be sorted according to the specified field, adding an uninterrupted line number to each row of the sorted result set, which is equivalent to a continuous id value.

For example, the sql statement: select ROW_NUMBER () over (order by studentno) id, * from Student, then the result set can be seen:

So we can see that the id values are contiguous, and all the subsequent stored procedures are relatively simple to write.

Note: we must give this result set a new name, such as temp, so the paging stored procedure can write:

If exists (select * from sysobjects where name='usp_getPageData') drop proc usp_getPageData-- delete gocreate proc usp_getPageData if there is a stored procedure named usp_getPageData-- create the name usp_getPageData stored procedure @ toPage int=0 output,-- Total pages @ pageIndex int= 1,-- default to display the first page @ pageCount int= 5-- default to 5 asselect temp.StudentNo,temp.LoginPwd,temp.StudentName,temp.Sex,temp.ClassId,temp.Phone,temp.Address records per page Temp.BornDate,temp.Email,temp.isDel from (select ROW_NUMBER () over (Order by studentno) id,* from Student) tempwhere id > (@ pageIndex-1) * @ pageCount and id

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.