|
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
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
