SqlServer 分页存储过程

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go





-- =============================================
-- Author:		<许刚>
-- Create date: <2011/9/22>
-- Description:	<通用存储过程>
-- =============================================
ALTER PROC [dbo].[sp_CommonPageSplit]
      @curPage INT,--当前页
      @pageSize INT,--没页数据数量
      @tbName VARCHAR(1000),--分页的表名
      @columNames VARCHAR(1000),--需要查询字段
      @orderBy VARCHAR(200),--排序方式
      @condition VARCHAR(2000),--查询条件
      @totalPage INT = 0 OUTPUT,@totalCount INT = 0 OUTPUT
AS 
      BEGIN
            DECLARE @curPageData INT
            DECLARE @sqlStr VARCHAR(6000)
            DECLARE @sqlCountStr NVARCHAR(4000)
            BEGIN
                  IF @condition != '' 
                     SET @condition = ' where ' + @condition
                  IF @orderBy != '' 
                     SET @orderBy = ' order by ' + @orderBy
                  PRINT @condition
                  SET @sqlCountStr = 'select @totalCount=count(1) from  ' + @tbName + @condition
                  EXEC sp_executesql @sqlCountStr,N'@totalCount int output',@totalCount OUT
                  IF @pageSize < 1 
                     BEGIN
                           SET @pageSize = 1
                     END
                  IF @totalCount % @pageSize = 0 
                     BEGIN
                           SET @totalPage = @totalCount / @pageSize
                     END
                  ELSE 
                     BEGIN
                           SET @totalPage = @totalCount / @pageSize + 1
                     END
                  IF @curPage > @totalPage 
                     BEGIN
                           SET @curPage = @totalPage
                     END
                  IF @curPage < 1 
                     BEGIN
                           SET @curPage = 1
                     END
                  SET @curPageData = @totalCount - ( @curPage - 1 ) * @pageSize
                  SET @sqlStr = '   With SQLPaging  
			As  
			(  
			Select Top(' + CAST(@pageSize * @curPage AS VARCHAR(60)) + ') ROW_NUMBER() OVER (' + @orderBy
                      + ') as resultNum,' + @columNames + '  
				FROM ' + @tbName + @condition + @orderBy + '  
			)  
			select * from SQLPaging where resultNum > ' + CAST(( @curPage - 1 ) * @pageSize AS VARCHAR(60)) 
                  PRINT @sqlStr
                  EXECUTE(@sqlStr)
            END
      END

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!