Good SQL paging stored procedures

Create PROCEDURE Sp_Conn_Sort
(
@ TblName varchar (255), -- table name

@ StrGetFields varchar (1000) = '*', -- the column to be returned

@ FldName varchar (255) = '', -- Name of the sorted Field

@ PageSize int = 40, -- 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) + ''+ @ strGetFields + '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) + ''+ @ strGetFields + 'from'

+ @ TblName + 'where' + @ fldName + ''+ @ strTmp + '(' + @ fldName + ') from (select top' + str (@ PageIndex-1) * @ PageSize) + ''+ @ fldName + 'from' + @ tblName +'' + @ strOrder + ') as tblTmp)' + @ strOrder

If @ strWhere! =''

Set @ strSQL = 'select top '+ str (@ PageSize) + ''+ @ strGetFields + 'from'

+ @ TblName + 'where' + @ fldName + ''+ @ strTmp + '('

+ @ FldName + ') from (select top' + str (@ PageIndex-1) * @ PageSize) +''

+ @ FldName + 'from' + @ tblName + 'where' + @ strWhere +''

+ @ StrOrder + ') as tblTmp) and' + @ strWhere + ''+ @ strOrder

End

End

Exec (@ strSQL)
-----------------

SQL server 2000 efficient paging Stored Procedure for a single primary key (Multi-field sorting supported)

Create PROC P_viewPage
/**//*
Nzperfect [no_mIss] efficient and common paging Stored Procedure (bidirectional retrieval) 2007.5.7 QQ: 34813284
Tip: Applicable to tables or views with a single primary key or a unique value column
Ps: the SQL statement is 8000 bytes. Note that the input parameters and total SQL length must not exceed the specified range.
*/
@ TableName VARCHAR (200), -- table name
@ FieldList VARCHAR (2000), -- displays the column name. If it is all fields, it is *
@ PrimaryKey VARCHAR (100), -- single primary key or unique value Key
@ Where VARCHAR (2000), -- the query condition does not contain the 'where' character, for example, id> 10 and len (userid)> 9
@ Order VARCHAR (1000), -- sorting does not contain the 'ORDER BY' character, such as id asc, userid desc. asc or desc must be specified.
-- Note that it takes effect when @ SortType = 3. Remember to add the primary key at the end; otherwise, it will make you depressed.
@ SortType INT, -- sorting Rule 1: Forward asc 2: reverse desc 3: Multi-column sorting method
@ RecorderCount INT, -- total number of records 0: the total number of records is returned.
@ PageSize INT, -- number of records output per page
@ PageIndex INT, -- current page number
@ TotalCount int output, -- Record total returned records
@ TotalPageCount int output -- total number of returned pages
AS
SET NOCOUNT ON
If isnull (@ TotalCount, '') ='' SET @ TotalCount = 0
SET @ Order = RTRIM (LTRIM (@ Order ))
SET @ PrimaryKey = RTRIM (LTRIM (@ PrimaryKey ))
SET @ FieldList = REPLACE (RTRIM (LTRIM (@ FieldList )),'','')
While charindex (',', @ Order)> 0 or CHARINDEX (',', @ Order)> 0
BEGIN
SET @ Order = REPLACE (@ Order ,',',',')
SET @ Order = REPLACE (@ Order ,',',',')
END
If isnull (@ TableName, '') ='' or ISNULL (@ FieldList, '') =''
Or ISNULL (@ PrimaryKey, '') =''
Or @ SortType <1 or @ SortType> 3
Or @ RecorderCount <0 or @ PageSize <0 or @ PageIndex <0
BEGIN
PRINT ('err _ 00 ')
RETURN
END
IF @ SortType = 3
BEGIN
IF (UPPER (RIGHT (@ Order, 4 ))! = 'Asc 'and upper (RIGHT (@ Order, 5 ))! = 'Desc ')
Begin print ('err _ 02 ') RETURN END
END
DECLARE @ new_where1 VARCHAR (1000)
DECLARE @ new_where2 VARCHAR (1000)
DECLARE @ new_order1 VARCHAR (1000)
DECLARE @ new_order2 VARCHAR (1000)
DECLARE @ new_order3 VARCHAR (1000)
DECLARE @ SQL VARCHAR (8000)
DECLARE @ SqlCount NVARCHAR (4000)
If isnull (@ where, '') =''
BEGIN
SET @ new_where1 =''
SET @ new_where2 = 'where'
END
ELSE
BEGIN
SET @ new_where1 = 'where' + @ Where
SET @ new_where2 = 'where' + @ Where + 'and'
END
If isnull (@ order, '') ='' or @ SortType = 1 or @ SortType = 2
BEGIN
IF @ SortType = 1
BEGIN
SET @ new_order1 = 'ORDER BY' + @ PrimaryKey + 'asc'
SET @ new_order2 = 'ORDER BY' + @ PrimaryKey + 'desc'
END
IF @ SortType = 2
BEGIN
SET @ new_order1 = 'ORDER BY' + @ PrimaryKey + 'desc'
SET @ new_order2 = 'ORDER BY' + @ PrimaryKey + 'asc'
END
END
ELSE
BEGIN
SET @ new_order1 = 'ORDER BY' + @ orDER
END

IF @ SortType = 3 and charindex (',' + @ PrimaryKey + '', ',' + @ Order)> 0
BEGIN
SET @ new_order1 = 'ORDER BY' + @ orDER
SET @ new_order2 = @ Order + ','
SET @ new_order2 = REPLACE (@ new_order2, 'asc, ',' {ASC}, '), 'desc',' {DESC },')
SET @ new_order2 = REPLACE (@ new_order2, '{ASC},', 'desc'), '{DESC},', 'asc ,')
SET @ new_order2 = 'ORDER BY' + SUBSTRING (@ new_order2, 1, LEN (@ new_order2)-1)
IF @ FieldList <> '*'
BEGIN
SET @ new_order3 = REPLACE (@ Order + ',', 'asc, ','), 'desc ,',',')
SET @ FieldList = ',' + @ FieldList
While charindex (',', @ new_order3)> 0
BEGIN
If charindex (SUBSTRING (',' + @ new_order3, 1, CHARINDEX (',', @ new_order3), ',' + @ FieldList + ',')> 0
BEGIN
SET @ FieldList =
@ FieldList + ',' + SUBSTRING (@ new_order3, 1, CHARINDEX (',', @ new_order3 ))
END
SET @ new_order3 =
SUBSTRING (@ new_order3, CHARINDEX (',', @ new_order3) + 1, LEN (@ new_order3 ))
END
SET @ FieldList = SUBSTRING (@ FieldList, 2, LEN (@ FieldList ))
END
END

SET @ SqlCount = 'select @ TotalCount = COUNT (*), @ TotalPageCount = CEILING (COUNT (*) + 0.0 )/'
+ CAST (@ PageSize as varchar) + ') FROM (Select * FROM' + @ TableName + @ new_where1 + ') AS t'
IF @ RecorderCount = 0
BEGIN
EXEC SP_EXECUTESQL @ SqlCount, n' @ TotalCount int output, @ TotalPageCount INT output ',
@ TotalCount OUTPUT, @ TotalPageCount OUTPUT
END
ELSE
BEGIN
Select @ TotalCount = @ RecorderCount
END
IF @ PageIndex> CEILING (@ TotalCount + 0.0)/@ PageSize)
BEGIN
SET @ PageIndex = CEILING (@ TotalCount + 0.0)/@ PageSize)
END
IF @ PageIndex = 1 or @ PageIndex> = CEILING (@ TotalCount + 0.0)/@ PageSize)
BEGIN
IF @ PageIndex = 1 -- returns the first page of data
BEGIN
SET @ SQL = 'select * FROM (Select TOP '+ STR (@ PageSize) + ''+ @ FieldList + 'from'
+ @ TableName + @ new_where1 + @ new_order1 + ') as tmp' + @ new_order1
END
IF @ PageIndex> = CEILING (@ TotalCount + 0.0)/@ PageSize) -- returns the last page of data
BEGIN
SET @ SQL = 'select TOP '+ STR (@ PageSize) + ''+ @ FieldList + 'from ('
+ 'Select top' + STR (ABS (@ PageSize * @ PageIndex-@ TotalCount-@ PageSize ))
+ ''+ @ FieldList + 'from'
+ @ TableName + @ new_where1 + @ new_order2 + ') as tmp'
+ @ New_order1
END
END
ELSE

BEGIN
IF @ SortType = 1 -- Only sort the primary key in positive order
BEGIN
IF @ PageIndex <= CEILING (@ TotalCount + 0.0)/@ PageSize)/2 -- forward search
BEGIN
SET @ SQL = 'select TOP '+ STR (@ PageSize) + ''+ @ FieldList + 'from'
+ @ TableName + @ new_where2 + @ PrimaryKey + '>'
+ '(Select MAX (' + @ PrimaryKey + ') FROM (Select TOP'
+ STR (@ PageSize * (@ PageIndex-1) + ''+ @ PrimaryKey
+ 'From' + @ TableName
+ @ New_where1 + @ new_order1 + ') as tmp)' + @ new_order1
END
ELSE -- reverse Retrieval
BEGIN
SET @ SQL = 'select TOP '+ STR (@ PageSize) + ''+ @ FieldList + 'from ('
+ 'Select TOP '+ STR (@ PageSize) +''
+ @ FieldList + 'from'
+ @ TableName + @ new_where2 + @ PrimaryKey + '<'
+ '(Select MIN (' + @ PrimaryKey + ') FROM (Select TOP'
+ STR (@ TotalCount-@ PageSize * @ PageIndex) + ''+ @ PrimaryKey
+ 'From' + @ TableName
+ @ New_where1 + @ new_order2 + ') as tmp)' + @ new_order2
+ ') As tmp' + @ new_order1
END
END
IF @ SortType = 2 -- Only sort the primary key in reverse order
BEGIN
IF @ PageIndex <= CEILING (@ TotalCount + 0.0)/@ PageSize)/2 -- forward search
BEGIN
SET @ SQL = 'select TOP '+ STR (@ PageSize) + ''+ @ FieldList + 'from'
+ @ TableName + @ new_where2 + @ PrimaryKey + '<'
+ '(Select MIN (' + @ PrimaryKey + ') FROM (Select TOP'
+ STR (@ PageSize * (@ PageIndex-1) + ''+ @ PrimaryKey
+ 'From' + @ TableName
+ @ New_where1 + @ new_order1 + ') as tmp)' + @ new_order1
END
ELSE -- reverse Retrieval
BEGIN
SET @ SQL = 'select TOP '+ STR (@ PageSize) + ''+ @ FieldList + 'from ('
+ 'Select TOP '+ STR (@ PageSize) +''
+ @ FieldList + 'from'
+ @ TableName + @ new_where2 + @ PrimaryKey + '>'
+ '(Select MAX (' + @ PrimaryKey + ') FROM (Select TOP'
+ STR (@ TotalCount-@ PageSize * @ PageIndex) + ''+ @ PrimaryKey
+ 'From' + @ TableName
+ @ New_where1 + @ new_order2 + ') as tmp)' + @ new_order2
+ ') As tmp' + @ new_order1
END
END
IF @ SortType = 3 -- Multi-column sorting, which must contain the primary key and be placed at the end; otherwise, it is not processed.
BEGIN
If charindex (',' + @ PrimaryKey + ',', '+ @ Order) = 0
Begin print ('err _ 02 ') RETURN END
IF @ PageIndex <= CEILING (@ TotalCount + 0.0)/@ PageSize)/2 -- forward search
BEGIN
SET @ SQL = 'select TOP '+ STR (@ PageSize) + ''+ @ FieldList + 'from ('
+ 'Select top' + STR (@ PageSize) + ''+ @ FieldList + 'from ('
+ 'Select top' + STR (@ PageSize * @ PageIndex) + ''+ @ FieldList
+ 'From' + @ TableName + @ new_where1 + @ new_order1 + ') as tmp'
+ @ New_order2 + ') as tmp' + @ new_order1
END
ELSE -- reverse Retrieval
BEGIN
SET @ SQL = 'select TOP '+ STR (@ PageSize) + ''+ @ FieldList + 'from ('
+ 'Select top' + STR (@ PageSize) + ''+ @ FieldList + 'from ('
+ 'Select top' + STR (@ TotalCount-@ PageSize * @ PageIndex + @ PageSize) + ''+ @ FieldList
+ 'From' + @ TableName + @ new_where1 + @ new_order2 + ') as tmp'
+ @ New_order1 + ') as tmp' + @ new_order1
END
END
END
PRINT (@ SQL)
EXEC (@ SQL)