SQL Server通用型分页存储过程

传参：pageIndex和pageSize

CREATE PROC [dbo].[Common_PageList]
(
@tab nvarchar(max),---表名
@strFld nvarchar(max), --字段字符串
@strWhere varchar(max), --where条件 
@PageIndex int, --页码
@PageSize int, --每页容纳的记录数
@Sort VARCHAR(255), --排序字段及规则,不用加order by
@IsGetCount bit  --是否得到记录总数，1为得到记录总数，0为不得到记录总数，返回记录集
)
AS
declare @strSql nvarchar(max)
set nocount on;
if(@IsGetCount = 1)
begin
 set @strSql='SELECT COUNT(0) FROM ' + @tab + ' WHERE ' + @strWhere
end
else
begin
  set @strSql=' SELECT * FROM (SELECT ROW_NUMBER() 
  OVER(ORDER BY ' + @Sort + ') AS rownum, ' + @strFld + ' FROM ' + @tab + ' where ' + @strWhere + ') AS Dwhere
  WHERE rownum BETWEEN ' + CAST(((@PageIndex-1)*@PageSize + 1) as nvarchar(20)) + ' and ' + cast((@PageIndex*@PageSize) as nvarchar(20))
end

exec (@strSql)

set nocount off;

 原文链接：https://www.cnblogs.com/sword-successful/p/4834309.html

转载于:https://www.cnblogs.com/xiaohong520789/p/9956711.html