可有存储过程分页查询案例

提问 3 340
煊煊
煊煊 VIP2 2023-05-21
版本:renren-cloud 3.11 开发环境:idea
能提供下存储过程分页查询案例?sql server存储过程
回帖
  • sunplay
    2023-05-25
    ALTER PROCEDURE [dbo].[prPager]( @PageIndex INT = 0, --起始位置 @PageSize INT = 10, --页大小,当小于等于0时显示所有记录 @TableName NVARCHAR(100), @WhereClause NVARCHAR(1000) = ' ', --条件语句 @OrderByClause NVARCHAR(200), --排序语句 @RowCount INT = 0 OUTPUT --总记录数 ) AS BEGIN -- Initialize Parameters IF (@PageIndex IS NULL) BEGIN SET @PageIndex=0 END IF (@PageSize IS NULL) BEGIN SET @PageSize=10 END IF(@WhereClause IS NULL OR @WhereClause='') BEGIN SET @WhereClause = ' 1=1 ' END IF(@OrderByClause IS NULL OR @OrderByClause = '') BEGIN SET @OrderByClause = ' [Id] DESC '; END -- Initialize Parameters DECLARE @Sql NVARCHAR(4000) IF(ISNULL(@RowCount,0) <= 0) BEGIN SET @Sql = 'SELECT @RowCount = COUNT(1) FROM '+@TableName+' WHERE ' + @WhereClause; EXEC sp_executesql @stmt = @sql, @params = N'@RowCount AS INT OUTPUT',@RowCount = @RowCount OUTPUT END IF(@PageSize > 0) BEGIN SET @Sql =( 'SELECT *' + ' FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY ' + @OrderByClause + ' ) AS ROWNUM'+ + ' FROM '+@TableName+' ' + ' WHERE ' + @WhereClause + ') AS A' + ' WHERE ROWNUM > '+STR(@PageSize*@PageIndex)+' AND ROWNUM <= '+STR(@PageSize*@PageIndex+@PageSize)) + ' ORDER BY '+@OrderByClause EXEC sp_executesql @Sql END ELSE BEGIN SET @Sql = 'SELECT * FROM '+@TableName+' WHERE ' + @WhereClause + ' ORDER BY '+ @OrderByClause; EXEC sp_executesql @Sql END END
    0 回复
  • sunplay
    2023-05-25
    看一下这个可不可以。SQL SERVER 版的
    1 回复
  • 煊煊
    煊煊 VIP2 (楼主)
    2023-06-02
    谢谢
    0 回复