SQL 存储过程[3] - 分页 - 滔Roy

SQL 存储过程[3] - 分页

SQL 存储过程[3]-分页

示例1：

/*  
分页，统计
*/ 
CREATE PROCEDURE Page  
@tblName varchar(255), -- 表名  
@fldName varchar(255), -- 字段名  
@PageSize int = 10, -- 页尺寸  
@PageIndex int = 1, -- 页码  
@IsCount bit = 0, -- 返回记录总数, 非 0 值则返回  
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序  
@strWhere varchar(1000) = '' , -- 查询条件 (注意: 不要加 where)  
@FieldsList varchar(255) -- 字段列表(要选出的字段)  
AS 
  
declare @strSQL varchar(1000), -- 主语句  
@strTmp varchar(300), -- 临时变量  
@strOrder varchar(400) -- 排序类型  
set @strTmp = '' 
set @strOrder = '' 
  
--只统计总记录数  
if @IsCount != 0  
begin 
if @strWhere != '' 
set @strTmp = " where (" + @strWhere + ")" 
set @strSQL = "select count(*) as Total from [" + @tblName + "]" + @strTmp  
end 
--不统计总记录,分页(查询表中所有记录)  
else 
begin 
--如果是第一页  
if @PageIndex = 1  
begin 
--排序  
if @OrderType != 0  
set @strOrder = " order by [" + @fldName +"] desc" 
else 
set @strOrder = " order by [" + @fldName +"] asc" 
--条件  
if @strWhere != '' 
set @strTmp = " where (" + @strWhere + ")" 
  
set @strSQL = "select top " + str(@PageSize) + " " +@FieldsList + " from [" 
+ @tblName + "]" + @strTmp + " " + @strOrder  
end 
--如果不是第一页  
else 
begin 
--排序  
if @OrderType != 0  
begin 
set @strTmp = "<(select min" 
set @strOrder = " order by [" + @fldName +"] desc" 
end 
else 
begin 
set @strTmp = ">(select max" 
set @strOrder = " order by [" + @fldName +"] asc" 
end 
set @strSQL = "select top " + str(@PageSize) + " " + @FieldsList + " from [" 
+ @tblName + "] where [" + @fldName + "]" + @strTmp + "([" 
+ @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " [" 
+ @fldName + "] from [" + @tblName + "]" + @strOrder + ") as tblTmp)" 
+ @strOrder  
--条件  
if @strWhere != '' 
set @strSQL = "select top " + str(@PageSize) + " " + @FieldsList + " from [" 
+ @tblName + "] where [" + @fldName + "]" + @strTmp + "([" 
+ @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " [" 
+ @fldName + "] from [" + @tblName + "] where (" + @strWhere + ") " 
+ @strOrder + ") as tblTmp) and (" + @strWhere + ") " + @strOrder  
end 
end 
exec (@strSQL) 

示例2：

100

101

102

103

104

105

CREATE PROCEDURE Rperp_Page_Data_GetList
@tbname     sysname,            --要分页显示的表名
@FieldKey   sysname,            --用于定位记录的主键(惟一键)字段,只能是单个字段
@PageCurrent int=1,              --要显示的页码
@PageSize   int=10,             --每页的大小(记录数)
@FieldShow  nvarchar(1000)='',   --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段
@FieldOrder  nvarchar(1000)='',  --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC用于指定排序顺序
@Where     nvarchar(1000)='' ,  --查询条件
@pagecount int output ,
@count int output
AS
DECLARE @sql nvarchar(4000)
SET NOCOUNT ON
--检查对象是否有效
IF OBJECT_ID(@tbname) IS NULL
BEGIN
RAISERROR(N'对象"%s"不存在',1,16,@tbname)
RETURN
END
IF OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTable')=0
AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsView')=0
AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTableFunction')=0
BEGIN
RAISERROR(N'"%s"不是表、视图或者表值函数',1,16,@tbname)
RETURN
END
 
--分页字段检查
IF ISNULL(@FieldKey,N'')=''
BEGIN
RAISERROR(N'分页处理需要主键（或者惟一键）',1,16)
RETURN
END
 
--其他参数检查及规范
IF ISNULL(@PageCurrent,0)<1 SET @PageCurrent=1
IF ISNULL(@PageSize,0)<1 SET @PageSize=10
IF ISNULL(@FieldShow,N'')=N'' SET @FieldShow=N'*'
IF ISNULL(@FieldOrder,N'')=N''
SET @FieldOrder=N''
ELSE
SET @FieldOrder=N'ORDER BY '+LTRIM(@FieldOrder)
IF ISNULL(@Where,N'')=N''
SET @Where=N''
ELSE
SET @Where=N'WHERE '+@Where
 
--如果@PageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值)
 
IF @PageCount IS NULL
BEGIN
SET @sql=N'SELECT @PageCount=COUNT(*)'
  +N' FROM '+@tbname
  +N' '+@Where
EXEC sp_executesql @sql,N'@PageCount int OUTPUT',@PageCount OUTPUT
SET @count=@PageCount
SET  @PageCount=(@PageCount+@PageSize-1)/@PageSize
END
 
 
 
--计算分页显示的TOPN值
DECLARE @TopN varchar(20),@TopN1 varchar(20)
SELECT @TopN=@PageSize,
@TopN1=@PageCurrent*@PageSize 
 
--第一页直接显示
IF @PageCurrent=1
 
EXEC(N'SELECT TOP '+@TopN
  +N' '+@FieldShow
  +N' FROM '+@tbname
  +N' '+@Where
  +N' '+@FieldOrder)
 
ELSE
BEGIN
SELECT @PageCurrent=@TopN1,
  @sql=N'SELECT @n=@n-1,@s=CASE WHEN @n<'+@TopN
   +N' THEN @s+N'',''+QUOTENAME(RTRIM(CAST('+@FieldKey
   +N' as varchar(8000))),N'''''''') ELSE N'''' END FROM '+@tbname
   +N' '+@Where
   +N' '+@FieldOrder
SET ROWCOUNT @PageCurrent
EXEC sp_executesql @sql,
  N'@n int,@s nvarchar(4000) OUTPUT',
  @PageCurrent,@sql OUTPUT
SET ROWCOUNT 0
IF @sql=N''
  EXEC(N'SELECT TOP 0'
   +N' '+@FieldShow
   +N' FROM '+@tbname)
ELSE
BEGIN
  SET @sql=STUFF(@sql,1,1,N'')  
  --执行查询
  EXEC(N'SELECT TOP '+@TopN
   +N' '+@FieldShow
   +N' FROM '+@tbname
   +N' WHERE '+@FieldKey
   +N' IN('+@sql
   +N') '+@FieldOrder)
END
END
GO

示例3：带偏移量的适合首页数据、非首页、不带偏移量等

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

USE Hotel
go
--带偏移量的适合首页数据的存储过程
CREATE PROC PaginationWithOffsetInFirstPage
    @columns VARCHAR(500) , --要显示的列名，用逗号隔开 
    @tableName VARCHAR(100) , --要查询的表名 
    @orderColumnName VARCHAR(100) , --排序的列名 
    @order VARCHAR(50) , --排序的方式，升序为asc,降序为 desc 
    @where VARCHAR(100) , --where 条件，如果不带查询条件，请用 1=1 
    @pageIndex INT , --当前页索引 
    @pageSize INT , --页大小(每页显示的记录条数) 
    @offset INT ,--偏移量(页中如果有别的数据需要插入，就赋此参数)
    @pageCount INT OUTPUT , --总页数，输出参数
    @totalCount INT OUTPUT --总记录数，输出参数 
AS
    BEGIN
        DECLARE @sqlRecordCount NVARCHAR(1000) --得到总记录条数的语句 
        DECLARE @sqlSelect NVARCHAR(1000) --查询语句 
        SET @sqlRecordCount = N'select @recordCount=count(*) from '
            + @tableName + ' where ' + @where
        DECLARE @recordCount INT --保存总记录条数的变量 
        EXEC sp_executesql @sqlRecordCount, N'@recordCount int output',
            @recordCount OUTPUT
--动态 sql 传参 
        SET @totalCount = @recordCount --把总记录数赋给输出参数
        IF ( @recordCount % @pageSize = 0 ) --如果总记录条数可以被页大小整除 
            SET @pageCount = @recordCount / @pageSize --总页数就等于总记录条数除以页大小 
        ELSE --如果总记录条数不能被页大小整除 
            SET @pageCount = @recordCount / @pageSize + 1 --总页数就等于总记录条数除以页大小加1 
        SET @sqlSelect = N'select top' + STR(@pageSize - @offset) + @columns
            + ' from ( 
select row_number() over (order by ' + @orderColumnName + ' ' + @order
            + ') as rowId,* from ' + @tableName + '  where ' + @where
            + ') as A where A.rowId >' + STR(( @pageindex - 1 ) * @pageSize)  
            --STR(( @pageIndex - 1 )* @pageSize + 1) + ' and '
            --+ STR(@pageIndex * @pageSize) 
        EXEC (@sqlSelect) --执行动态Sql 
    END
go 
--以下是调用示例
USE Hotel
go
CREATE PROC PaginationWithOffsetNotInFirstPage
    @columns VARCHAR(500) , --要显示的列名，用逗号隔开 
    @tableName VARCHAR(100) , --要查询的表名 
    @orderColumnName VARCHAR(100) , --排序的列名 
    @order VARCHAR(50) , --排序的方式，升序为asc,降序为 desc 
    @where VARCHAR(100) , --where 条件，如果不带查询条件，请用 1=1 
    @pageIndex INT , --当前页索引 
    @pageSize INT , --页大小(每页显示的记录条数) 
    @offset INT ,--偏移量(页中如果有别的数据需要插入，就赋此参数,默认为零)
    @pageCount INT OUTPUT , --总页数，输出参数
    @totalCount INT OUTPUT --总记录数，输出参数 
AS
    BEGIN
        DECLARE @sqlRecordCount NVARCHAR(1000) --得到总记录条数的语句 
        DECLARE @sqlSelect NVARCHAR(1000) --查询语句 
        SET @sqlRecordCount = N'select @recordCount=count(*) from '
            + @tableName + ' where ' + @where
        DECLARE @recordCount INT --保存总记录条数的变量 
        EXEC sp_executesql @sqlRecordCount, N'@recordCount int output',
            @recordCount OUTPUT
--动态 sql 传参 
        SET @totalCount = @recordCount --把总记录数赋给输出参数
        IF ( @recordCount % @pageSize = 0 ) --如果总记录条数可以被页大小整除 
            SET @pageCount = @recordCount / @pageSize --总页数就等于总记录条数除以页大小 
        ELSE --如果总记录条数不能被页大小整除 
            SET @pageCount = @recordCount / @pageSize + 1 --总页数就等于总记录条数除以页大小加1 
        SET @sqlSelect = N'select top' + STR(@pageSize) + @columns + ' from ( 
select row_number() over (order by ' + @orderColumnName + ' ' + @order
            + ') as rowId,* from ' + @tableName + ' where ' + @where
            + ') as A where A.rowId >' + STR(( @pageindex - 1 ) * @pageSize
                                             - @offset)  
            --STR(( @pageIndex - 1 )* @pageSize + 1) + ' and '
            --+ STR(@pageIndex * @pageSize) 
        EXEC (@sqlSelect) --执行动态Sql 
    END
go  
USE Hotel 
go 
 
--调用首页数据的存储过程,page必须传1
DECLARE @d DATETIME
SET @d = GETDATE()
 
DECLARE @pageCount INT
DECLARE @totalCount NVARCHAR(1000) 
EXECUTE dbo.PaginationWithOffsetInFirstPage 'rowid,[hotelId],[hotelName]', -- varchar(500)
    'dbo.TB_HotelList', -- varchar(100)
    'hotelId', -- varchar(100)
    'asc', -- varchar(50)
    '1=1 and cityId=215', -- varchar(100)
    1, -- int
    20, -- int
    5,
    @pageCount OUTPUT, -- int
    @totalCount OUTPUT
SELECT  STR(@pageCount) ,
        @totalCount
SELECT  '语句执行花费时间(毫秒)' = DATEDIFF(ms, @d, GETDATE())
    go
  
  
  
--调用非首页数据的存储过程
DECLARE @pageCount INT
DECLARE @totalCount NVARCHAR(1000) 
EXECUTE dbo.PaginationWithOffsetNotInFirstPage 'rowid,[hotelId],[hotelName]', -- varchar(500)
    'dbo.TB_HotelList', -- varchar(100)
    'hotelId', -- varchar(100)
    'asc', -- varchar(50)
    '1=1 and cityId=215', -- varchar(100)
    3, -- int
    20, -- int
    5,
    @pageCount OUTPUT, -- int
    @totalCount OUTPUT
SELECT  STR(@pageCount) ,
        @totalCount   
         
         
--不带偏移量的通用 存储过程
CREATE PROC Pagination
    @columns VARCHAR(500) , --要显示的列名，用逗号隔开 
    @tableName VARCHAR(100) , --要查询的表名 
    @orderColumnName VARCHAR(100) , --排序的列名 
    @order VARCHAR(50) , --排序的方式，升序为asc,降序为 desc 
    @where VARCHAR(100) , --where 条件，如果不带查询条件，请用 1=1 
    @pageIndex INT , --当前页索引 
    @pageSize INT , --页大小(每页显示的记录条数) 
    @pageCount INT OUTPUT , --总页数，输出参数
    @totalCount INT OUTPUT --总记录数，输出参数 
AS
    BEGIN
        DECLARE @sqlRecordCount NVARCHAR(1000) --得到总记录条数的语句 
        DECLARE @sqlSelect NVARCHAR(1000) --查询语句 
        SET @sqlRecordCount = N'select @recordCount=count(*) from '
            + @tableName + ' where ' + @where
        DECLARE @recordCount INT --保存总记录条数的变量 
        EXEC sp_executesql @sqlRecordCount, N'@recordCount int output',
            @recordCount OUTPUT
--动态 sql 传参 
        SET @totalCount = @recordCount --把总记录数赋给输出参数
        IF ( @recordCount % @pageSize = 0 ) --如果总记录条数可以被页大小整除 
            SET @pageCount = @recordCount / @pageSize --总页数就等于总记录条数除以页大小 
        ELSE --如果总记录条数不能被页大小整除 
            SET @pageCount = @recordCount / @pageSize + 1 --总页数就等于总记录条数除以页大小加1 
        SET @sqlSelect = N'select top' + STR(@pageSize) + @columns
            + ' from ( 
select row_number() over (order by ' + @orderColumnName + ' ' + @order
            + ') as rowId,* from ' + @tableName + ' with(nolock) where ' + @where
            + ') as A  where A.rowId >' + STR(( @pageindex - 1 ) * @pageSize)  
            --STR(( @pageIndex - 1 )* @pageSize + 1) + ' and '
            --+ STR(@pageIndex * @pageSize) 
        EXEC (@sqlSelect) --执行动态Sql 
    END
go

创建时间：2020.09.24　　更新时间：

posted on 2020-09-24 08:48 滔Roy 阅读(228) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

滔 Roy

SQL 存储过程[3] - 分页

导航

搜索

我的标签

积分与排名

随笔分类

随笔档案

阅读排行榜

评论排行榜

推荐排行榜

最新评论