分页存储过程大全，整理过了

数据分页存储过程集合.sql

USE AdventureWorks2008
GO
SELECT COUNT( * ) FROM Production.TransactionHistoryArchive
GO
SELECT TOP 50 * FROM Production.TransactionHistoryArchive
ORDER BY ReferenceOrderID ASC
GO

-- Use Top * Top
DECLARE @Start datetime,@end datetime;
SET @Start = getdate();

DECLARE @PageNumber INT, @Count INT, @Sql varchar(max);
SET @PageNumber = 5000 ;
SET @Count = 10 ;
SET @Sql = ' SELECT T2.* FROM (
SELECT TOP 10 T1. * FROM
(SELECT TOP ' + STR(@PageNumber*@Count) + ' * FROM Production.TransactionHistoryArchive
ORDER BY ReferenceOrderID ASC) AS T1
ORDER BY ReferenceOrderID DESC) AS T2
ORDER BY ReferenceOrderID ASC ' ;
EXEC (@sql);

SET @end = getdate();
PRINT Datediff(millisecond,@Start,@end);
GO

-- USE table value sql2005
DECLARE @Start datetime,@end datetime;
SET @Start = getdate();
DECLARE @PageNumber INT, @Count INT, @Sql varchar(max);
SET @PageNumber = 5000 ;
SET @Count = 10 ;

DECLARE @local_variable table (RowNumber int identity( 1 , 1 ),[TransactionID] [ int ],
[ProductID] [ int ],
[ReferenceOrderID] [ int ],
[ReferenceOrderLineID] [ int ],
[TransactionDate] [datetime],
[TransactionType] [nchar]( 1 ),
[Quantity] [ int ],
[ActualCost] [money],
[ModifiedDate] [datetime]);
insert into @local_variable (TransactionID, ProductID, ReferenceOrderID, ReferenceOrderLineID, TransactionDate, TransactionType, Quantity, ActualCost, ModifiedDate)
SELECT TOP 50000 TransactionID, ProductID, ReferenceOrderID, ReferenceOrderLineID, TransactionDate, TransactionType, Quantity, ActualCost, ModifiedDate from Production.TransactionHistoryArchive ORDER BY ReferenceOrderID ASC
select * from @local_variable where RowNumber > (@PageNumber - 1 ) * @Count and RowNumber <= @PageNumber * @Count

SET @end = getdate();
PRINT Datediff(millisecond,@Start,@end);
GO

-- USE temp table
DECLARE @Start datetime,@end datetime;
SET @Start = getdate();
DECLARE @PageNumber INT, @Count INT, @Sql varchar(max);
SET @PageNumber = 5000 ;
SET @Count = 10 ;

create table #local_variable(RowNumber int identity( 1 , 1 ),[TransactionID] [ int ],
[ProductID] [ int ],
[ReferenceOrderID] [ int ],
[ReferenceOrderLineID] [ int ],
[TransactionDate] [datetime],
[TransactionType] [nchar]( 1 ),
[Quantity] [ int ],
[ActualCost] [money],
[ModifiedDate] [datetime]);
insert into #local_variable (TransactionID, ProductID, ReferenceOrderID, ReferenceOrderLineID, TransactionDate, TransactionType, Quantity, ActualCost, ModifiedDate)
SELECT TOP 50000 TransactionID, ProductID, ReferenceOrderID, ReferenceOrderLineID, TransactionDate, TransactionType, Quantity, ActualCost, ModifiedDate from Production.TransactionHistoryArchive ORDER BY ReferenceOrderID ASC
select * from #local_variable where RowNumber > (@PageNumber - 1 ) * @Count and RowNumber <= @PageNumber * @Count

SET @end = getdate();
PRINT Datediff(millisecond,@Start,@end);
GO

-- Use ROW_NUMBER
DECLARE @Start datetime,@end datetime;
SET @Start = getdate();

DECLARE @PageNumber INT, @Count INT, @Sql varchar(max);
SET @PageNumber = 5000 ;
SET @Count = 10 ;
SELECT * FROM
( SELECT ROW_NUMBER()
OVER(ORDER BY ReferenceOrderID) AS RowNumber,
*
FROM Production.TransactionHistoryArchive) AS T
WHERE T.RowNumber <= @PageNumber * @Count AND T.RowNumber > (@PageNumber - 1 ) * @Count;

SET @end = getdate();
PRINT Datediff(millisecond,@Start,@end);
GO

 

 

分页存储过程大全，整理过了

 

P_viewPage

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go




ALTER PROC [dbo].[P_viewPage]
/*
declare @totalCount int
declare @totalPageCount int
exec P_viewPage 'gnschq','*','xh','','asc',1,0,3,100,@totalCount output,@totalPageCount output
print @totalCount
print @totalPageCount
*/
/**//**//**//*
nzperfect [no_mIss] 高效通用分页存储过程(双向检索) 2007.5.7 QQ:34813284
敬告：适用于单一主键或存在唯一值列的表或视图
ps:Sql语句为8000字节,调用时请注意传入参数及sql总长度不要超过指定范围


*/

@TableName VARCHAR( 200 ), -- 表名
@FieldList VARCHAR( 2000 ), -- 显示列名，如果是全部字段则为 *
@PrimaryKey VARCHAR( 100 ), -- 单一主键或唯一值键
@Where VARCHAR( 2000 ), -- 查询条件 不含 ' where ' 字符，如id > 10 and len(userid) > 9
@Order VARCHAR( 1000 ), -- 排序 不含 ' order by ' 字符，如id asc,userid desc，必须指定asc或desc
-- 注意当@SortType = 3时生效，记住一定要在最后加上主键，否则会让你比较郁闷
@SortType INT, -- 排序规则 1 :正序asc 2 :倒序desc 3 :多列排序方法
@RecorderCount INT, -- 记录总数 0 :会返回总记录
@PageSize INT, -- 每页输出的记录数
@PageIndex INT, -- 当前页数
@TotalCount INT OUTPUT, -- 记返回总记录
@TotalPageCount INT OUTPUT -- 返回总页数
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(REPLACE(@new_order2, ' ASC, ' , ' {ASC}, ' ), ' DESC, ' , ' {DESC}, ' )
SET @new_order2 = REPLACE(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(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 ' + @TableName + @new_where1

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 -- 返回第一页数据
BEGIN
SET @Sql = ' SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '
+ @TableName + @new_where1 + @new_order1
END
IF @PageIndex >= CEILING((@TotalCount + 0.0 ) / @PageSize) -- 返回最后一页数据
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 -- 仅主键正序排序
BEGIN
IF @PageIndex <= CEILING((@TotalCount + 0.0 ) / @PageSize) / 2 -- 正向检索
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 -- 反向检索
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 -- 仅主键反序排序
BEGIN
IF @PageIndex <= CEILING((@TotalCount + 0.0 ) / @PageSize) / 2 -- 正向检索
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 -- 反向检索
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 -- 多列排序，必须包含主键，且放置最后，否则不处理
BEGIN
IF CHARINDEX( ' , ' + @PrimaryKey + ' ' , ' , ' + @Order) = 0
BEGIN PRINT( ' ERR_02 ' ) RETURN END
IF @PageIndex <= CEILING((@TotalCount + 0.0 ) / @PageSize) / 2 -- 正向检索
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 -- 反向检索
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)

 

Paging_Asc_Desc.sql

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go







ALTER PROCEDURE [dbo].[Paging_Asc_Desc] (
@Tables varchar( 1000 ),
@PK varchar( 100 ),
@Sort varchar( 200 ) = NULL,
@PageNumber int = 1 ,
@PageSize int = 10 ,
@Fields varchar( 1000 ) = ' * ' ,
@Filter varchar( 1000 ) = NULL,
@Group varchar( 1000 ) = NULL)
AS

/* Find the @PK type */
DECLARE @PKTable varchar( 100 )
DECLARE @PKName varchar( 100 )
DECLARE @type varchar( 100 )
DECLARE @prec int

IF CHARINDEX( ' . ' , @PK) > 0
BEGIN
SET @PKTable = SUBSTRING(@PK, 0 , CHARINDEX( ' . ' ,@PK))
SET @PKName = SUBSTRING(@PK, CHARINDEX( ' . ' ,@PK) + 1 , LEN(@PK))
END
ELSE
BEGIN
SET @PKTable = @Tables
SET @PKName = @PK
END

SELECT @type = t.name, @prec = c.prec
FROM sysobjects o
JOIN syscolumns c on o.id = c.id
JOIN systypes t on c.xusertype = t.xusertype
WHERE o.name = @PKTable AND c.name = @PKName

IF CHARINDEX( ' char ' , @type) > 0
SET @type = @type + ' ( ' + CAST(@prec AS varchar) + ' ) '

DECLARE @strPageSize varchar( 50 )
DECLARE @strRows varchar( 50 )
DECLARE @strFilter varchar( 8000 )
DECLARE @strGroup varchar( 8000 )
DECLARE @strSortColumn varchar( 4000 )
DECLARE @strSortDesc varchar( 4000 )

/* Default Sorting */
IF @Sort IS NULL
SET @Sort = @PK

/* Set sorting variables. */
IF CHARINDEX( ' DESC ' ,@Sort) > 0
BEGIN
SET @strSortDesc = REPLACE(@Sort, ' DESC ' , ' ASC ' )
SET @strSortColumn = ' , ' + REPLACE(@Sort, ' DESC ' , '' )
END
ELSE
BEGIN
IF CHARINDEX( ' ASC ' , @Sort) = 0
BEGIN
SET @strSortDesc = @Sort + ' DESC '
SET @strSortColumn = ' , ' + @Sort
END
ELSE
BEGIN
SET @strSortDesc = REPLACE(@Sort, ' ASC ' , ' DESC ' )
SET @strSortColumn = ' , ' + REPLACE(@Sort, ' ASC ' , '' )
END
END
IF @Sort = @PK
SET @strSortColumn = ''

/* Default Page Number */
IF @PageNumber < 1
SET @PageNumber = 1

/* Set paging variables. */
SET @strPageSize = CONVERT(varchar( 50 ), @PageSize)
SET @strRows = CONVERT(varchar( 50 ), (@PageSize * (@PageNumber - 1 ) + @PageSize))

/* Set filter & group variables. */
IF @Filter IS NOT NULL AND @Filter != ''
BEGIN
SET @strFilter = ' WHERE ' + @Filter + ' '
END
ELSE
BEGIN
SET @strFilter = ''
END
IF @Group IS NOT NULL AND @Group != ''
SET @strGroup = ' GROUP BY ' + @Group + ' '
ELSE
SET @strGroup = ''

-- print ' DECLARE @tblPK TABLE (
-- PK ' + @type + ' NOT NULL PRIMARY KEY
-- )
--
-- INSERT INTO @tblPK SELECT TOP ' + @strPageSize + ' ' + @PK + ' FROM (SELECT TOP ' + @strRows + ' ' + @PK + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + ' ) AS ' + @PKTable + ' ORDER BY ' + @strSortDesc + '
--
-- SELECT ' + @Fields + ' FROM ' + @Tables + ' JOIN @tblPK tblPK ON ' + @PK + ' = tblPK.PK ' + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort

/* Execute dynamic query */
EXEC(
' DECLARE @tblPK TABLE (
PK ' + @type + ' NOT NULL PRIMARY KEY
)

INSERT INTO @tblPK SELECT TOP ' + @strPageSize + ' ' + @PK + ' FROM (SELECT TOP ' + @strRows + ' ' + @PK + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + ' ) AS ' + @PKTable + ' ORDER BY ' + @strSortDesc + '

SELECT ' + @Fields + ' FROM ' + @Tables + ' JOIN @tblPK tblPK ON ' + @PK + ' = tblPK.PK ' + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort
)

 

Paging_Cursor.sql

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go





ALTER PROCEDURE [dbo].[Paging_Cursor] (
@Tables varchar( 1000 ),
@PK varchar( 100 ),
@Sort varchar( 200 ) = NULL,
@PageNumber int = 1 ,
@PageSize int = 10 ,
@Fields varchar( 1000 ) = ' * ' ,
@Filter varchar( 1000 ) = NULL,
@Group varchar( 1000 ) = NULL)
AS

/* Find the @PK type */
DECLARE @PKTable varchar( 100 )
DECLARE @PKName varchar( 100 )
DECLARE @type varchar( 100 )
DECLARE @prec int

IF CHARINDEX( ' . ' , @PK) > 0
BEGIN
SET @PKTable = SUBSTRING(@PK, 0 , CHARINDEX( ' . ' ,@PK))
SET @PKName = SUBSTRING(@PK, CHARINDEX( ' . ' ,@PK) + 1 , LEN(@PK))
END
ELSE
BEGIN
SET @PKTable = @Tables
SET @PKName = @PK
END

SELECT @type = t.name, @prec = c.prec
FROM sysobjects o
JOIN syscolumns c on o.id = c.id
JOIN systypes t on c.xusertype = t.xusertype
WHERE o.name = @PKTable AND c.name = @PKName

IF CHARINDEX( ' char ' , @type) > 0
SET @type = @type + ' ( ' + CAST(@prec AS varchar) + ' ) '

DECLARE @strPageSize varchar( 50 )
DECLARE @strStartRow varchar( 50 )
DECLARE @strFilter varchar( 1000 )
DECLARE @strGroup varchar( 1000 )

/* Default Sorting */
IF @Sort IS NULL OR @Sort = ''
SET @Sort = @PK

/* Default Page Number */
IF @PageNumber < 1
SET @PageNumber = 1

/* Set paging variables. */
SET @strPageSize = CAST(@PageSize AS varchar( 50 ))
SET @strStartRow = CAST(((@PageNumber - 1 ) * @PageSize + 1 ) AS varchar( 50 ))

/* Set filter & group variables. */
IF @Filter IS NOT NULL AND @Filter != ''
SET @strFilter = ' WHERE ' + @Filter + ' '
ELSE
SET @strFilter = ''
IF @Group IS NOT NULL AND @Group != ''
SET @strGroup = ' GROUP BY ' + @Group + ' '
ELSE
SET @strGroup = ''

/* Execute dynamic query */
EXEC(
' DECLARE @PageSize int
SET @PageSize = ' + @strPageSize + '

DECLARE @PK ' + @type + '
DECLARE @tblPK TABLE (
PK ' + @type + ' NOT NULL PRIMARY KEY
)

DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
SELECT ' + @PK + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '

OPEN PagingCursor
FETCH RELATIVE ' + @strStartRow + ' FROM PagingCursor INTO @PK

SET NOCOUNT ON

WHILE @PageSize > 0 AND @@FETCH_STATUS = 0
BEGIN
INSERT @tblPK (PK) VALUES (@PK)
FETCH NEXT FROM PagingCursor INTO @PK
SET @PageSize = @PageSize - 1
END

CLOSE PagingCursor
DEALLOCATE PagingCursor

SELECT ' + @Fields + ' FROM ' + @Tables + ' JOIN @tblPK tblPK ON ' + @PK + ' = tblPK.PK ' + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort
)

 

Paging_RowCount.sql

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


---------------------------------------------------------------
-- 分页存储过程(使用RowCount) -- edit by SiBen
-- summary:
-- 获取表或表集合的分页数据
-- 当多表连接时,sort列必须指定表名
---------------------------------------------------------------

ALTER PROCEDURE [dbo].[Paging_RowCount]
(
@Tables varchar( 1000 ),
@PK varchar( 100 ),
@Sort varchar( 200 ) = NULL,
@PageNumber int = 1 ,
@PageSize int = 10 ,
@Fields varchar( 1000 ) = ' * ' ,
@Filter varchar( 1000 ) = NULL,
@Group varchar( 1000 ) = NULL,
@RecordCount int = 0 output
)
AS

/* Default Sorting */
IF @Sort IS NULL OR @Sort = ''
SET @Sort = @PK

/* Find the @PK type */
DECLARE @SortTable varchar( 100 )
DECLARE @SortName varchar( 100 )
DECLARE @PKTable varchar( 100 )
DECLARE @PKName varchar( 100 )
DECLARE @strSortColumn varchar( 200 )
DECLARE @operator char ( 2 )
DECLARE @type varchar( 100 )
DECLARE @prec int

/* Set sorting variables. */
IF CHARINDEX( ' DESC ' ,@Sort) > 0
BEGIN
SET @strSortColumn = REPLACE(@Sort, ' DESC ' , '' )
SET @operator = ' < '
END
ELSE
BEGIN
IF CHARINDEX( ' ASC ' , @Sort) > 0
SET @strSortColumn = REPLACE(@Sort, ' ASC ' , '' )
ELSE
SET @strSortColumn = @Sort

SET @operator = ' > '
END

/* Set PK,Sort name */
IF CHARINDEX( ' . ' , @strSortColumn) > 0
BEGIN
SET @SortTable = SUBSTRING(@strSortColumn, 0 , CHARINDEX( ' . ' ,@strSortColumn))
SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX( ' . ' ,@strSortColumn) + 1 , LEN(@strSortColumn))
END
ELSE
BEGIN
SET @SortTable = @Tables
SET @SortName = @strSortColumn
END
IF CHARINDEX( ' . ' , @PK) > 0
BEGIN
SET @PKTable = SUBSTRING(@PK, 0 , CHARINDEX( ' . ' ,@PK))
SET @PKName = SUBSTRING(@PK, CHARINDEX( ' . ' ,@PK) + 1 , LEN(@PK))
END
ELSE
BEGIN
SET @PKTable = @Tables
SET @PKName = @PK
END

SELECT @type = t.name, @prec = c.prec
FROM sysobjects o
JOIN syscolumns c on o.id = c.id
JOIN systypes t on c.xusertype = t.xusertype
WHERE o.name = @SortTable AND c.name = @SortName

IF CHARINDEX( ' char ' , @type) > 0
SET @type = @type + ' ( ' + CAST(@prec AS varchar) + ' ) '

DECLARE @strPageSize varchar( 50 )
DECLARE @strStartRow varchar( 50 )
DECLARE @strFilter varchar( 1000 )
DECLARE @strSimpleFilter varchar( 1000 )
DECLARE @strGroup varchar( 1000 )

/* Default Page Number */
IF @PageNumber < 1
SET @PageNumber = 1

/* Set paging variables. */
SET @strPageSize = CAST(@PageSize AS varchar( 50 ))
SET @strStartRow = CAST(((@PageNumber - 1 ) * @PageSize + 1 ) AS varchar( 50 ))

/* Set filter & group variables. */
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 = ''

/* Get rows count. */
DECLARE @str_Count_SQL nvarchar( 500 )
SET @str_Count_SQL = ' SELECT @TotalCount=count(*) FROM ' + @Tables + @strFilter
EXEC sp_executesql @str_Count_SQL,N ' @TotalCount int=0 output ' ,@RecordCount output

-- print '
-- 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 + '
-- '

/* Execute dynamic query */
IF @PKTable = @SortTable and @PKName = @SortName
BEGIN
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 + '
'
)
END
ELSE
BEGIN
/* Get PK Type */
DECLARE @pktype varchar( 100 )
DECLARE @pkprec int

SELECT @pktype = t.name, @pkprec = c.prec
FROM sysobjects o
JOIN syscolumns c on o.id = c.id
JOIN systypes t on c.xusertype = t.xusertype
WHERE o.name = @PKTable AND c.name = @PKName

IF CHARINDEX( ' char ' , @pktype) > 0
SET @pktype = @pktype + ' ( ' + CAST(@pkprec AS varchar) + ' ) '

/* Execute dynamic query */
EXEC(
'
DECLARE @SortColumn ' + @type + '
DECLARE @SortNullValue ' + @type + '
DECLARE @PKStartValue ' + @pktype + '
SET @SortNullValue = CAST( '''' as ' + @type + ' )
SET ROWCOUNT ' + @strStartRow + '
SELECT @SortColumn = isNull( ' + @strSortColumn + ' ,@SortNullValue), @PKStartValue = ' + @PK + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + ' , ' + @PK + ' Desc
SET ROWCOUNT ' + @strPageSize + '
SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE (isNull( ' + @strSortColumn+ ' ,@SortNullValue) ' + @operator + ' @SortColumn or (isNull( ' + @strSortColumn+ ' ,@SortNullValue) = @SortColumn and ' + @PK + ' <= @PKStartValue)) ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + ' , ' + @PK + ' Desc
'
)
END

 

Paging_RowCount_Simple.sql

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



ALTER PROCEDURE [dbo].[Paging_RowCount_Simple]
(
@Tables varchar( 1000 ),
@PK varchar( 100 ),
@Sort varchar( 200 ) = NULL,
@PageNumber int = 1 ,
@PageSize int = 10 ,
@Fields varchar( 1000 ) = ' * ' ,
@Filter varchar( 1000 ) = NULL,
@Group varchar( 1000 ) = NULL)
AS
/* Default Sorting */
IF @Sort IS NULL OR @Sort = ''
SET @Sort = @PK

print @Filter
/* Find the @PK type */
DECLARE @SortTable varchar( 100 )
DECLARE @SortName varchar( 100 )
DECLARE @strSortColumn varchar( 200 )
DECLARE @operator char ( 2 )
DECLARE @type varchar( 100 )
DECLARE @prec int
/* Set sorting variables. */
IF CHARINDEX( ' DESC ' ,@Sort) > 0
BEGIN
SET @strSortColumn = REPLACE(@Sort, ' DESC ' , '' )
SET @operator = ' <= '
END
ELSE
BEGIN
IF CHARINDEX( ' ASC ' , @Sort) = 0
SET @strSortColumn = REPLACE(@Sort, ' ASC ' , '' )
SET @operator = ' >= '
END

IF CHARINDEX( ' . ' , @strSortColumn) > 0
BEGIN
SET @SortTable = SUBSTRING(@strSortColumn, 0 , CHARINDEX( ' . ' ,@strSortColumn))
SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX( ' . ' ,@strSortColumn) + 1 , LEN(@strSortColumn))
END
ELSE
BEGIN
SET @SortTable = @Tables
SET @SortName = @strSortColumn
END

SELECT @type = t.name, @prec = c.prec
FROM sysobjects o
JOIN syscolumns c on o.id = c.id
JOIN systypes t on c.xusertype = t.xusertype
WHERE o.name = @SortTable AND c.name = @SortName


IF CHARINDEX( ' char ' , @type) > 0
SET @type = @type + ' ( ' + CAST(@prec AS varchar) + ' ) '

DECLARE @strPageSize varchar( 50 )
DECLARE @strStartRow varchar( 50 )
DECLARE @strFilter varchar( 1000 )
DECLARE @strSimpleFilter varchar( 1000 )
DECLARE @strGroup varchar( 1000 )

/* Default Page Number */
IF @PageNumber < 1
SET @PageNumber = 1

/* Set paging variables. */
SET @strPageSize = CAST(@PageSize AS varchar( 50 ))
SET @strStartRow = CAST(((@PageNumber - 1 ) * @PageSize + 1 ) AS varchar( 50 ))

/* Set filter & group variables. */
IF @Filter IS NOT NULL AND @Filter != ''
BEGIN
SET @strFilter = ' WHERE ' + @Filter + ' '
SET @strSimpleFilter = ' AND ' + @Filter + ' '
END
ELSE
BEGIN
SET @strSimpleFilter = ''
SET @strFilter = ''
END

print @strFilter

IF @Group IS NOT NULL AND @Group != ''
SET @strGroup = ' GROUP BY ' + @Group + ' '
ELSE
SET @strGroup = ''



/* Execute dynamic query */
-- print '
-- 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 + '
-- '

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 + '
'
)

 

Paging_SubQuery.sql

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go





ALTER PROCEDURE [dbo].[Paging_SubQuery] (
@Tables varchar( 1000 ),
@PK varchar( 100 ),
@Sort varchar( 200 ) = NULL,
@PageNumber int = 1 ,
@PageSize int = 10 ,
@Fields varchar( 1000 ) = ' * ' ,
@Filter varchar( 1000 ) = NULL,
@Group varchar( 1000 ) = NULL)
AS

DECLARE @strPageSize varchar( 50 )
DECLARE @strSkippedRows varchar( 50 )
DECLARE @strFilter varchar( 1000 )
DECLARE @strSimpleFilter varchar( 1000 )
DECLARE @strGroup varchar( 1000 )

/* Default Sorting */
IF @Sort IS NULL OR @Sort = ''
SET @Sort = @PK
SET @Sort = ' ORDER BY ' + @Sort + ' '

/* Default Page Number */
IF @PageNumber < 1
SET @PageNumber = 1

/* Set paging variables. */
SET @strPageSize = CONVERT(varchar( 50 ), @PageSize)
SET @strSkippedRows = CONVERT(varchar( 50 ), @PageSize * (@PageNumber - 1 ))

/* Set filter & group variables. */
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 = ''


IF @PageNumber = 1 -- In this case we can execute a more efficient query with no subqueries.
EXEC (
' SELECT TOP ' + @strPageSize + ' ' + @Fields + ' FROM ' + @Tables +
@strFilter + @strGroup + @Sort
)
ELSE -- Execute a structure of subqueries that brings the correct page.
EXEC (
' SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @PK + ' IN ' + '
(SELECT TOP ' + @strPageSize + ' ' + @PK + ' FROM ' + @Tables +
' WHERE ' + @PK + ' NOT IN
(SELECT TOP ' + @strSkippedRows + ' ' + @PK + ' FROM ' + @Tables +
@strFilter + @strGroup + @Sort + ' ) ' +
@strSimpleFilter +
@strGroup +
@Sort + ' ) ' +
@strGroup +
@Sort
)

 

转载于:https://www.cnblogs.com/liulf/archive/2010/04/16/1713877.html