Two types of MSSQL paging Stored Procedure instance applications

Two types of MSSQL paging Stored Procedure instance applications & lt; htmlxmlns & quot; & gt; & lt; head & gt; & lt; metahttp-equiv & quot; content-Type & quot; content & quot; texthtml; charsetgb2312 & quot; & gt; & lt; title & gt; two types of MSSQL paging Stored Procedure instances application two types of MSSQL paging Stored Procedure instance applications



Two types of MSSQL paging Stored Procedure instance applications

In small data website applications, MSSQL does not need to store stored procedures on pages. The stored procedures are only used for hundreds of thousands of pages and tens of millions of pages,

Create a 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 (such as TABLE. FLDNAME format)
@ 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
Declare @ fldName_t varchar (255) -- Name of the sort field used for paging, excluding the table name when multiple tables are tied
Set @ fldName_t = right (@ fldName, len (@ fldName)-CHARINDEX ('.', @ fldName ))
If @ doCount! = 0
Begin
If @ strWhere! =''
Set @ strSQL = 'select count (*) as Total from '+ @ tblName +' where
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) +' + 'from' + @ tblName + 'where' + @ strWhere + ''+ @ strOrder
Else
Set @ strSQL = 'select top '+ str (@ PageSize) +' + 'from' + @ tblName + ''+ @ strOrder
-- Execute the above Code on the first page, which will speed up the execution.
End
Else
Begin
-- The following code gives @ strSQL the SQL code to be actually executed
Set @ strSQL = 'select top '+ str (@ PageSize) + '+ 'from' + @ tblName + 'where' + @ fldName + ''+ @ strTmp +' ('+ @ fldName_t + ') from (select top '+ str (@ PageIndex-1) * @ PageSize) + ''+ @ fldName + 'from' + @ tblName +'' + @ strOrder + ') as tblTmp) '+ @ strOrder
If @ strWhere! =''
Set @ strSQL = 'select top '+ str (@ PageSize) + '+ 'from' + @ tblName + 'where' + @ fldName + ''+ @ strTmp +' ('+ @ fldName_t + ') from (select top '+ str (@ PageIndex-1) * @ PageSize) + ''+ @ fldName + 'from' + @ tblName + 'where' + @ strWhere +'' + @ strOrder + ') as tblTmp) and '+ @ strWhere + ''+ @ strOrder
End
End
Exec (@ strSQL)
Go

-- Test
Create table news -- create a table
(
N_id int iDENTITY (1, 1) primary key,
N_title char (200 ),
N_content text
)

-- Insert 1000000 data records in a write Loop
Create proc tt
As
Declare @ I int
Set @ I = 0
While (@ I <1000000)
Begin
Insert into news (n_title, n_content) values ('SB ', 'dsfsdfsd ')
Set @ I = @ I + 1
End
Exec tt

Exec pagination 'News', '*', 'n' _ id', 0 ,''

MSSQL paging stored procedures, support for connection queries and other complex queries

Call Method

Exec Pagination 'select * from [order] ', 20, 0, 'createtime ASC'

Exec Pagination 'select * from [order] ', 20, 0, 'createtime ASC, Money desc'

Instance process

Create procedure Pagination
(
@ SQL nvarchar (1024), -- query statement
@ PageSize int = 20, -- page size
@ PageIndex int = 0, -- paging Index
@ Sort nvarchar (100) = '', -- Sort Field
@ TotalCount int = 0 output -- total
)
AS

Set nocount on
/* Declare the query string */
Declare @ strSQL nvarchar (4000)

Set @ strSQL = 'select @ TotalCount = count (*) from () as t'

/* Total Number of query results obtained */
Exec sp_executesql
@ StrSQL,
Int = 0 output ',
@ TotalCount = @ TotalCount OUTPUT

Declare @ ItemCount int
Declare @ _ PageIndex int

Set @ _ PageIndex = @ PageIndex + 1;
/* Determine the search boundary */
Set @ ItemCount = @ TotalCount-@ PageSize * @ _ PageIndex

If (@ ItemCount <0)
Set @ ItemCount = @ ItemCount + @ PageSize
Else
Set @ ItemCount = @ PageSize

If (@ ItemCount <0) return 1

If (@ Sort! = '')
Begin
/* Declare the sorting variable */
Declare @ IndexSort1 nvarchar (50), @ IndexSort2 nvarchar (50), @ Sort1 nvarchar (50), @ Sort2 nvarchar (50)

SET @ Sort1 = @ Sort
SET @ Sort2 = Replace (@ Sort, 'desc',), 'asc', 'desc'), 'asc ')

Set @ strSQL = 'select * FROM
(Select top '+ STR (@ ItemCount) +' * FROM
(Select top '+ STR (@ PageSize * @ _ PageIndex) +' * FROM
() AS t0
Order by + ') AS t1
Order by + ') AS t2
Order'
End
Else
Begin
Set @ strSQL = 'select * FROM
(Select top '+ STR (@ ItemCount) +' * FROM
(Select top '+ STR (@ PageSize * @ _ PageIndex) +' * FROM
() As t0)
AS t1)
AS t2'
End

Exec sp_executesql
@ StrSQL
GO
/*
The advantage of stored procedures is that the internal functions of mssql run very quickly.
*/?>