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结果如图所示