Create PROC [dbo].[ProcPageList] (
@pageSize INT = 20, –每页记录数
@pageNo INT =1, –当前页
@tableName VARCHAR(50), –表名
@whereString VARCHAR(800) = ‘ 1=1 ‘,–WHERE条件
@orderString VARCHAR(200), –排序条件(倒序需要带desc)
@recordTotal INT OUTPUT –输出记录总数
)
AS
BEGIN
DECLARE @tempSql NVARCHAR(4000)
–输出参数@recordTotal为总记录数
SET @tempSql = N‘select @recordTotal = count(*) from ‘ + @tableName + ‘ where ‘+ @whereString
EXEC sp_executesql @tempSql,N‘@recordTotal INT OUTPUT‘,@recordTotal OUTPUT
–主查询返回结果集
SET @tempSql = N‘select * from (select *,Row_number() over(order by ‘+@orderString+‘) as rows from ‘+@tableName+‘ WHERE ‘+@whereString+‘) as main_temp where rows BETWEEN ‘+ convert(varchar(100),(@pageNo – 1) * @pageSize+ 1) + ‘ and ‘ + convert(varchar(100),@pageNo * @pageSize)
EXEC(@tempSql)
END