SQL Server 千萬數量級 分頁 存儲過程

　　隨著信息時代的發展信息系統的使用越來越多信息量越來越大當信息量越來越大這在數據展示特別是報表這塊對系統展現效率要求越來越高對於千萬級數據量的展示必須得使用分頁來展示
If object_id(SP_Pagination)is not null
drop proc SP_Pagination
go
Create PROCEDURE SP_Pagination
/**//*
***************************************************************
** 千萬數量級分頁存儲過程 **
***************************************************************
參數說明:
Tables :表名稱視圖(試圖這邊目前還有點小問題)
PrimaryKey :主關鍵字
Sort :排序語句不帶Order By 比如NewsID DescOrderRows Asc
CurrentPage :當前頁碼
PageSize :分頁尺寸
Filter :過濾語句不帶Where
Group :Group語句不帶Group By
***************************************************************/
(
@Tables varchar()
@PrimaryKey varchar()
@Sort varchar() = NULL
@CurrentPage int =
@PageSize int =
@Fields varchar() = *
@Filter varchar() = NULL
@Group varchar() = NULL
)
AS
/**//*默認排序*/
IF @Sort IS NULL OR @Sort =
SET @Sort = @PrimaryKey
DECLARE @SortTable varchar()
DECLARE @SortName varchar()
DECLARE @strSortColumn varchar()
DECLARE @operator char()
DECLARE @type varchar()
DECLARE @prec int
/**//*設定排序語句*/
IF CHARINDEX(DESC@Sort)>
BEGIN
SET @strSortColumn = REPLACE(@Sort DESC )
SET @operator = <=
END
ELSE
BEGIN
IF CHARINDEX(ASC @Sort) =
print
print REPLACE(@Sort ASC )
SET @strSortColumn = REPLACE(@Sort ASC )
print @strSortColumn
SET @operator = >=
print @operator
END
IF CHARINDEX( @strSortColumn) >
BEGIN
SET @SortTable = SUBSTRING(@strSortColumn CHARINDEX(@strSortColumn))
SET @SortName = SUBSTRING(@strSortColumn CHARINDEX(@strSortColumn) + LEN(@strSortColumn))
END
ELSE
BEGIN
SET @SortTable = @Tables
SET @SortName = @strSortColumn
print @SortTable
print @SortName
END
SELECT @type=tname @prec=cprec
FROM sysobjects o
JOIN syscolumns c on oid=cid
JOIN systypes t on cxusertype=txusertype
WHERE oname = @SortTable AND cname = @SortName
print @type
print @prec
IF CHARINDEX(char @type) >
SET @type = @type + ( + CAST(@prec AS varchar) + )
DECLARE @strPageSize varchar()
DECLARE @strStartRow varchar()
DECLARE @strFilter varchar()
DECLARE @strSimpleFilter varchar()
DECLARE @strGroup varchar()
/**//*默認當前頁*/
IF @CurrentPage <
SET @CurrentPage =
/**//*設置分頁參數*/
SET @strPageSize = CAST(@PageSize AS varchar())
SET @strStartRow = CAST(((@CurrentPage )*@PageSize + ) AS varchar())
/**//*篩選以及分組語句*/
IF @Filter IS NOT NULL AND @Filter !=
BEGIN
SET @strFilter = WHERE + @Filter +
SET @strSimpleFilter = AND + @Filter +
END
ELSE
BEGIN
SET @strSimpleFilter =
SET @strFilter =
END
IF @Group IS NOT NULL AND @Group !=
SET @strGroup = GROUP BY + @Group +
ELSE
SET @strGroup =
/*print @type
print @strStartRow
print @strSortColumn
print @Tables
print @strFilter
print @strGroup
print @Sort*/
/**//*執行查詢語句*/
EXEC(

DECLARE @SortColumn + @type +
SET ROWCOUNT + @strStartRow +
SELECT @SortColumn= + @strSortColumn + FROM + @Tables + @strFilter + + @strGroup + ORDER BY + @Sort +
SET ROWCOUNT + @strPageSize +
SELECT + @Fields + FROM + @Tables + WHERE + @strSortColumn + @operator + @SortColumn + @strSimpleFilter + + @strGroup + ORDER BY + @Sort +

)

下面是在數據庫查詢分析器裡面怎麼調用這個存儲過程的實例如圖所示為Traffic_Sites原表查詢的結果集 以Traffic_Sites表為例執行分頁存儲過程腳本exec SP_Pagination Traffic_SitesIdId asc* 執行上述SQL結果如圖所示  

　　

From:http://tw.wingwit.com/Article/program/SQLServer/201311/22256.html