隨著信息時代的發展信息系統的使用越來越多
If object_id(
drop proc SP_Pagination
go
Create PROCEDURE SP_Pagination
/**//*
***************************************************************
** 千萬數量級分頁存儲過程 **
***************************************************************
參數說明:
***************************************************************/
(
@Tables varchar(
@PrimaryKey varchar(
@Sort varchar(
@CurrentPage int =
@PageSize int =
@Fields varchar(
@Filter varchar(
@Group varchar(
)
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(
BEGIN
SET @strSortColumn = REPLACE(@Sort
SET @operator =
END
ELSE
BEGIN
IF CHARINDEX(
print
print REPLACE(@Sort
SET @strSortColumn = REPLACE(@Sort
print @strSortColumn
SET @operator =
print @operator
END
IF CHARINDEX(
BEGIN
SET @SortTable = SUBSTRING(@strSortColumn
SET @SortName = SUBSTRING(@strSortColumn
END
ELSE
BEGIN
SET @SortTable = @Tables
SET @SortName = @strSortColumn
print @SortTable
print @SortName
END
SELECT @type=t
FROM sysobjects o
JOIN syscolumns c on o
JOIN systypes t on c
WHERE o
IF CHARINDEX(
SET @type = @type +
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
/**//*篩選以及分組語句
IF @Filter IS NOT NULL AND @Filter !=
BEGIN
SET @strFilter =
SET @strSimpleFilter =
END
ELSE
BEGIN
SET @strSimpleFilter =
SET @strFilter =
END
IF @Group IS NOT NULL AND @Group !=
SET @strGroup =
ELSE
SET @strGroup =
/*print @type
print @strStartRow
print @strSortColumn
print @Tables
print @strFilter
print @strGroup
print @Sort*/
/**//*執行查詢語句*/
EXEC(
DECLARE @SortColumn
SET ROWCOUNT
SELECT @SortColumn=
SET ROWCOUNT
SELECT
)
![](http://img.educity.cn/article_image/2013082620/324011203629.png)
From:http://tw.wingwit.com/Article/program/SQLServer/201311/22256.html