SQL Server two paging stored procedures use introduction, server Stored Procedures

SQL Server two paging stored procedures use introduction, server Stored Procedures

Because many companies now have a written examination for recruitment to write a paging storage process, some companies even require candidates to implement paging in two ways, if pagination is not used in the project, many applicants may encounter some problems. The following describes two paging methods.

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.

Requirement: query the student information. Five records are displayed on each page.

Ii. method 1 paging: Use the subquery not in

For example:

Page 1

Select top 5 * from Student

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

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

Likewise, you can get the third page ,,,,,,,

I believe everyone can understand this method. This paging stored procedure will not be described much, but we will focus on the paging method below.

3. The second method is paging: Use ROW_NUMBER () as the built-in function.

Because a function dedicated to paging is provided after 05, that is, the ROW_NUMBER () function. The basic syntax of paging is: ROW_NUMBER () over (sorting field ): you can sort by specified fields and add an uninterrupted row number to each row of the sorted result set, which is equivalent to the same continuous id value,

For example, if the SQL statement is select ROW_NUMBER () over (order by studentno) id, * from Student, you can see the result set:

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

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

If exists (select * from sysobjects where name = 'usp _ getpagedata ') drop proc usp_getPageData -- if a stored procedure named usp_getPageData exists, delete gocreate proc usp_getPageData -- create the Stored Procedure named usp_getPageData @ toPage int = 0 output, -- total number of pages @ pageIndex int = 1, -- the first page is displayed by default @ pageCount int = 5 -- five asselect temp records are displayed on each page by default. 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) tempwhere id> (@ pageIndex-1) * @ pageCount and id <= @ pageIndex * @ pageCountset @ toPage = ceiling (select COUNT (*) from Student) * 1.0/@ pageCount) -- use the ceiling function to calculate the total number of pages go

This indicates that in actual project development, the total number of pages is frequently displayed to users. The toPage parameter is added for all stored procedures, because it is output to the user, all parameter types are defined as output and assigned values using set.

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

How can I use stored procedure query to define a paging stored procedure in sqlserver?

Try:

Private DataTable GetDataByPageProc (String TableName, String Primarykey, String FieldsName, String ByWHERE
, String ByOrder, int PageSize, int PageIndex, ref int RecordCount, ref int PageCount)
{
SqlConnection cn = new SqlConnection (Function. ConnectionString );
String SQL = "exec P_SlipPage @ TableName = @ a, @ Primarykey = @ B, @ FieldsName = @ c, @ ByWHERE = @ d, @ ByOrder = @ e, @ PageSize = @ f, @ PageIndex = @ g, @ RecordCount = @ h, @ PageCount = @ I ";
Using (SqlDataAdapter da = new SqlDataAdapter (SQL, cn ))
{
Da. SelectCommand. Parameters. AddWithValue ("@ a", TableName );
Da. SelectCommand. Parameters. AddWithValue ("@ B", Primarykey );
Da. SelectCommand. Parameters. AddWithValue ("@ c", FieldsName );
Da. SelectCommand. Parameters. AddWithValue ("@ d", ByWHERE );
Da. SelectCommand. Parameters. AddWithValue ("@ e", ByOrder );
Da. SelectCommand. Parameters. AddWithValue ("@ f", PageSize );
Da. SelectCommand. Parameters. AddWithValue ("@ g", PageIndex );
Da. SelectCommand. Parameters. AddWithValue ("@ h", RecordCount );
Da. SelectCommand. Parameters. AddWithValue ("@ I", PageCount );
DataTable dt = new DataTable ();
Da. Fill (dt );
Return dt;
}
}... Remaining full text>
 
SQL server paging Stored Procedure

Create procedure pagination
@ TblName varchar (255), -- table name
@ StrGetFields varchar (1000) = '*', -- the column to be returned
@ FldName varchar (255) = '', -- Name of the sorted Field
@ PageSize int = 10, -- page size
@ PageIndex int = 1, -- page number
@ DoCount bit = 0, -- returns the total number of records. If the value is not 0, the system returns
@ OrderType bit = 0, -- set the sorting type. If the value is not 0, the sorting type is descending.
@ StrWhere varchar (1500) = ''-- Query condition (Note: Do not add where)
AS
Declare @ strSQL varchar (5000) -- subject sentence
Declare @ strTmp varchar (110) -- Temporary Variable
Declare @ strOrder varchar (400) -- sort type
If @ doCount! = 0
Begin
If @ strWhere! =''
Set @ strSQL = 'select count (*) as Total from ['+ @ tblName +'] where' + @ strWhere
Else
Set @ strSQL = 'select count (*) as Total from ['+ @ tblName +']'
End
-- The above Code indicates that if @ doCount is not passed over 0, the total number of statistics will be executed. All the code below is 0 @ doCount
Else
Begin
If @ OrderType! = 0
Begin
Set @ strTmp = '<(select min'
Set @ strOrder = 'order by ['+ @ fldName +'] desc'
-- If @ OrderType is not 0, execute the descending order. This sentence is very important!
End
Else
Begin
Set @ strTmp = '> (select max'
Set @ strOrder = 'order by ['+ @ fldName +'] asc'
End
If @ PageIndex = 1
Begin
If @ strWhere! =''
Set @ strSQL = 'select top '+ str (@ PageSize) + ''+ @ strGetFields + 'from ['+ @ tblName +'] where' + @ strWhere +'' + @ strOrder
Else
Set @ strSQL = 'select top '+ str (@ PageSize) + ''+ @ strG ...... the remaining full text>