--通用分页
ALTER PROCEDURE [dbo].[Sys_Pagination_1]
@tblName VARCHAR(2000) , -- 表名
@strGetFields VARCHAR(1000) = '*' , -- 需要返回的列
@fldName VARCHAR(255) = '' , -- 排序的字段名
@PageSize INT = 10 , -- 页尺寸
@PageIndex INT = 1 , -- 页码
@OrderType BIT = 0 , -- 设置排序类型, 非 0 值则降序
@strWhere VARCHAR(2500) = '' -- 查询条件 (注意: 不要加 where)
AS
BEGIN
--处理开始点和结束点
DECLARE @strOrder VARCHAR(400) -- 排序类型
DECLARE @MaxCode NVARCHAR(100) --记录个数
DECLARE @StartRecord INT;
DECLARE @EndRecord INT;
DECLARE @TotalCountSql NVARCHAR(4000);
DECLARE @SqlString NVARCHAR(4000);
SET @StartRecord = ( @PageIndex - 1 ) * @PageSize + 1
SET @EndRecord = @StartRecord + @PageSize - 1
IF @OrderType != 0
BEGIN
SET @strOrder = ' order by ' + @fldName + ' desc'
--如果@OrderType不是0，就执行降序，这句很重要！
END
ELSE
BEGIN
SET @strOrder = ' order by ' + @fldName + ' asc'
END
SET @TotalCountSql = N'select @Max_ID = count(*) from ' + @tblName;--总记录数语句
SET @SqlString = N'(select row_number() over (' + @strOrder
+ ') as rowId,' + @strGetFields + ' from ' + @tblName;--查询语句
IF ( @strWhere ! = ''
OR @strWhere != NULL
)
BEGIN
SET @TotalCountSql = @TotalCountSql + ' where ' + @strWhere;
SET @SqlString = @SqlString + ' where ' + @strWhere;
END
--第一次执行得到
--IF(@TotalRecord is null)
-- BEGIN
EXEC sp_executesql @totalCountSql, N'@Max_ID varchar(100) output',
@MaxCode OUTPUT;--返回总记录数
PRINT @totalCountSql
-- END
----执行主语句
SET @SqlString = 'select ' + @strGetFields + ' from ' + @SqlString
+ ') as t where rowId between ' + LTRIM(STR(@StartRecord))
+ ' and ' + LTRIM(STR(@EndRecord));
EXEC(@SqlString)
PRINT @SqlString
RETURN @MaxCode
END