分页查询存储过程
/****** Object: StoredProcedure [dbo].[pagination3] Script Date: 2019/1/11 9:02:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[pagination3] --新建存储过程用:CREATE PROCEDURE [dbo].[pagination3]
@tblName varchar(50), --表名
@strGetFields varchar(5000) = '*', --字段名(全部字段为*)
@fldName varchar(5000), --排序字段(必须!支持多字段)
@strWhere varchar(5000) = Null,--条件语句(不用加where)
@pageSize int, --每页多少条记录
@pageIndex int = 1 , --指定当前为第几页
@OrderType bit=0, -- 设置排序类型, 非 0 值则降序
@doCount bit = 0
as
begin
Declare @sql nvarchar(4000)
--计算总记录数
if @doCount != 0
begin
if (@strWhere='' or @strWhere=NULL)
set @sql = 'select count(*) as Total from [' + @tblName + ']'
else
set @sql = 'select count(*) as Total from [' + @tblName + '] where '+@strWhere
end
else
begin
if (@strWhere='' or @strWhere=NULL)
if(@OrderType=1)
set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @fldName + ' desc) as rowId,' + @strGetFields + ' from ' + @tblName
else
set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @fldName + ' asc) as rowId,' + @strGetFields + ' from ' + @tblName
else
if(@OrderType=1)
set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @fldName + ' desc) as rowId,' + @strGetFields + ' from ' + @tblName + ' where ' + @strWhere
else
set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @fldName + ' asc) as rowId,' + @strGetFields + ' from ' + @tblName + ' where ' + @strWhere
--处理开始点和结束点
Declare @StartRecord int
Declare @EndRecord int
set @StartRecord = (@pageIndex-1)*@PageSize + 1
set @EndRecord = @StartRecord + @pageSize - 1
--继续合成sql语句
if(@OrderType=1)
set @Sql = @Sql + ') as ' + @tblName + ' where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' + Convert(varchar(50),@EndRecord) + 'order by '+@fldName+' desc'
else
set @Sql = @Sql + ') as ' + @tblName + ' where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' + Convert(varchar(50),@EndRecord) + 'order by '+@fldName+' asc'
end
end
Exec(@sql)