SQL paging stored procedures support connection queries and other complex SQL statements

CREATEPROCEDUREPagination (& nbsp; @ SQLnvarchar (1024), & nbsp; @ PageSizeint20, & nbsp; -- page size & nbsp; @ PageIndexint0, & nbsp; -- paging index & nbsp; @ Sortnvarc create procedure Pagination
(
@ SQL nvarchar (1024 ),
@ 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 '')

Print @ Sort1
Print @ Sort2

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) </