SqlServer 分页存储过程

概述set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo-- =============================================-- Author: <许刚>-- Create date: <2011/9/22>-- Description: <通用存储过程>-- ===========================

set ANSI_NulLS ONset QUOTED_IDENTIFIER ONgo-- =============================================-- 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 OUTPUTAS       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

总结

以上是内存溢出为你收集整理的SqlServer 分页存储过程全部内容，希望文章能够帮你解决SqlServer 分页存储过程所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。