AspNetPagerÓëMSSQLº£Á¿·ÖÒ³´æ´¢¹ý³Ì½áºÏ

´æ´¢¹ý³ÌPagination£º
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Pagination]
@tblName varchar(255), -- ±íÃû
@PKName varchar(255), -- ×Ö¶ÎÃû
@strGotFields varchar(1000) = '*' , --²éѯ×Ö¶ÎÃû
@PageSize int = 10, -- Ò³³ß´ç
@PageIndex int = 1, -- Ò³Âë
@OrderType bit = 0, -- ÉèÖÃÅÅÐòÀàÐÍ, ·Ç 0 ÖµÔò½µÐò
@strSort varchar(255) = '', --ÅÅÐò×Ö¶Î
@strWhere varchar(2000) = '', -- ²éѯÌõ¼þ (×¢Òâ: ²»Òª¼Ó where)
@isCount bit = 1 --È¡µÃ¼Ç¼ÌõÊý
AS
DECLARE @strSQL varchar(6000) -- Ö÷Óï¾ä
DECLARE @strTmp varchar(1000) -- ÁÙʱ±äÁ¿
DECLARE @strOrder varchar(500) -- ÅÅÐòÀàÐÍ
DECLARE @strCount varchar(1000)
DECLARE @fldName varchar(255)
DECLARE @sortName varchar(255)
DECLARE @countSQL varchar(1000)
SET @fldName = @PKName
IF @strSort != ''
BEGIN
SET @sortName = @strSort
END
ELSE
BEGIN
SET @sortName = @PKName
END
IF @isCount = 1
BEGIN
IF @strWhere != ''
BEGIN
SET @countSQL = 'select count(' + @fldName + ') from ' + @tblName + ' where ' + @strWhere
END
ELSE
BEGIN
SET @countSQL = 'select count(' + @fldName + ') from ' + @tblName
END
EXEC (@countSQL)
RETURN
END
ELSE
BEGIN
IF @OrderType != 0
BEGIN
SET @strTmp = '<(select min'
SET @strOrder = ' order by ' + @sortName + ' desc'
END
ELSE
BEGIN
SET @strTmp = '>(select max'
SET @strOrder = ' order by ' + @sortName +' asc'
END
SET @strSQL = 'select top ' + str(@PageSize) + @strGotFields + ' 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) + @strGotFields + ' from '
+ @tblName + ' where ' + @fldName + '' + @strTmp + '('
+ @fldName + ') from (select top ' + str((@PageIndex-1)*@PageSize) + ' '
+ @fldName + ' from ' + @tblName + ' where ' + @strWhere + ' '
+ @strOrder + ')