相信很多人都曾为分页发愁过,上网百度后有各种各样的例子,但不是不能运行就是篇幅太长很难看懂。我以前也喜欢琢磨这个,不过大多都是在程序里面写的,在此分享一个SQL Server数据库的分页存储过程。喜欢的请给我点个赞!
CREATE PROCEDURE [dbo].[sp_GetPagingList]
@Table nvarchar(max), --表名
@Field nvarchar(1000) = '*', --查询条件
@Where nvarchar(max) = NULL, --Where条件
@GroupBy nvarchar(500) = NULL, --分组条件
@OrderBy nvarchar(500), --排序条件(不可为空)
@PageNumber int, --开始页码
@PageSize int, --页大小
@IsCount bit --是否返回记录总数
AS
BEGIN
------------------------------------------------------------------------------------------------
DECLARE @strWhere nvarchar(max) --Where 条件
IF @Where IS NOT NULL AND @Where != ''
BEGIN
SET @strWhere = ' WHERE ' + @Where + ' '
END
ELSE
BEGIN
SET @strWhere = ''
END
------------------------------------------------------------------------------------------------
DECLARE @strGroupBy nvarchar(500) --GroupBy 条件
IF @GroupBy IS NOT NULL AND @GroupBy != ''
BEGIN
SET @strGroupBy = ' GROUP BY ' + @GroupBy + ' '
END
ELSE
BEGIN
SET @strGroupBy = ''
END
------------------------------------------------------------------------------------------------
DECLARE @strOrderBy nvarchar(500) --OrderBy 条件
SET @strOrderBy = ' ORDER BY ' + @OrderBy
------------------------------------------------------------------------------------------------
DECLARE @strSql nvarchar(max) --Sql 语句
------------------------------------------------------------------------------------------------
IF @PageNumber < 1 --第一页提高性能
BEGIN
SET @PageNumber = 1
END
IF @PageNumber = 1
BEGIN
SET @strSql = 'SELECT TOP ' + str(@PageSize) + ' ' + @Field + ' FROM ' + @Table +
@strWhere + @strGroupBy + @strOrderBy
EXEC sp_executesql @strSql
END
------------------------------------------------------------------------------------------------
ELSE
BEGIN
DECLARE @STARTID nvarchar(50)
DECLARE @ENDID nvarchar(50)
SET @STARTID = convert(nvarchar(50),(@PageNumber - 1) * @PageSize + 1)
SET @ENDID = convert(nvarchar(50),@PageNumber * @PageSize)
SET @strSql = 'WITH MYTABLE AS (SELECT ROW_NUMBER() OVER (' + @strOrderBy + ')
AS RowNumber,' + @Field + ' FROM '+ @Table + @strWhere + @strGroupBy + ')
SELECT * FROM MYTABLE
WHERE RowNumber BETWEEN ' + @STARTID + ' AND ' + @ENDID
EXEC sp_executesql @strSql
END
------------------------------------------------------------------------------------------------
--计算总行数
IF @IsCount = 1
BEGIN
IF @strGroupBy IS NOT NULL AND @strGroupBy != '' --GroupBy 条件
BEGIN
SET @strSql= ' select Count (1) AS RecordCount FROM (SELECT 1 AS RecordCount FROM '+
@Table + @strWhere + @strGroupBy + ') as tbcount '
END
ELSE
BEGIN
SET @strSql= ' SELECT Count (1) AS RecordCount FROM ' + @Table + @strWhere
END
EXEC sp_executesql @strSql
END
RETURN
END
在这里提醒下大家,分页最好还是写在数据库里的存储过程,可以提升读取效率。以上就是我现在用的分页,如有不对,欢迎拍砖,力争设计出更好的分页存储过程!
分页的样式大家可以用bootstrap里面的,挺不错的,另外阿里的前端大牛贤心也设计了一款不错的分页插件laypage,大家可以看看!