代码语言：javascript复制
create procedure SqlDataPaging
@tbName varchar(255), --表名
@tbFields varchar(1000), --返回字段
@OrderField varchar(255), --排序的字段名
@PageSize int, --页尺寸
@PageIndex int, --页码
@OrderType bit, --排序类型,1是升序,0是降序
@strWhere varchar(1500), --查询条件
@Total int output --返回总记录数
as
declare @strSql varchar(5000) --主语句
declare @strOrder varchar(200) --排序
declare @strSqlCount nvarchar(500)--查询记录总数主语句
declare @strtemp varchar(50) --排序附加语句
--------------排序-----------------1是降序,0未升序
if @OrderType !=0
begin
set @strtemp= '>(select max('
set @strOrder='order by ' @OrderField ' asc '
end
else
begin
set @strtemp= '<(select min('
set @strOrder='order by ' @OrderField ' desc'
end
--------------总记录数---------------
if @strWhere !=''
begin
set @strSqlCount='Select @TotalCout=count(*) from ' @tbName 'where ' @strWhere
end
else
begin
set @strSqlCount='Select @TotalCout=count(*) from ' @tbName
end
--------------如果是第一页------------
if @PageIndex =1
begin
if @strWhere !=''
begin
set @strSql = 'Select top' str(@PageSize) ' ' @tbFields ' from ' @tbName ' where ' @strWhere ' ' @strOrder
end
else
begin
set @strSql = 'Select top' str(@PageSize) ' ' @tbFields ' from ' @tbName ' ' @strOrder
end
end
else
begin
if @strWhere !=''
begin
set @strSql='Select top ' str(@PageSize) ' ' @tbFields
' from ' @tbName ' where ' @OrderField ' '
@strtemp ' ' @OrderField ') from (select top'
str((@PageIndex-1)*@PageSize) ' ' @OrderField ' from ' @tbName
@strWhere ' ' @strOrder ') as tb)' @strOrder
end
else
begin
set @strSql='Select top ' str(@PageSize) ' ' @tbFields
' from ' @tbName ' where ' @OrderField ' '
@strtemp ' ' @OrderField ') from (select top'
str((@PageIndex-1)*@PageSize) ' ' @OrderField ' from ' @tbName
' ' @strOrder ') as tb)' @strOrder
end
end
exec sp_executesql @strSqlCount,N'@TotalCout int output',@Total output
exec(@strSql)发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/100555.html原文链接：
