set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[V3_GetPageList]
@tblName varchar(255), -- 表名
@strFields varchar(2000), -- 需要返回的列,默认*
@strOrder varchar(2000), -- 排序的字段名,必填
@strOrderType varchar(10), -- 排序的方式,默认ASC
@PageSize int , -- 页尺寸,默认10
@PageIndex int , -- 页码,默认1
@strWhere nvarchar(max), -- 查询条件 (注意: 不要加 where)
@pagecount int output-- 总记录数
AS
declare @strSQL varchar(5000)
declare @sql nvarchar(1000)
declare @recount int
Begin
if @strWhere !=''
begin
set @strWhere=' where '+@strWhere
end
if @PageSize is null
begin
set @PageSize = 10
end
if @PageIndex is null
begin
set @PageIndex = 1
end
--获得总记录数
set @sql = 'SELECT @con =count(1) FROM '+@tblName+''+@strWhere +''
exec sp_executesql @sql,N'@con int output',@recount output
set @pagecount =@recount
--获得需要的分页数据信息
set @strSQL=
'SELECT *,'+cast(@pagecount as varchar(50))+' as pagecount FROM ('+
'SELECT ROW_NUMBER() OVER (ORDER BY '+@strOrder+''+@strOrderType+') AS pos,'+@strFields+' '+
'FROM '+@tblName+' '+@strWhere+
') AS sp WHERE pos BETWEEN '+str((@PageIndex-1)*@PageSize+1)+' AND '+str(@PageIndex*@PageSize)
exec (@strSQL)
END