/********************************************************************************** Function: PagedProc ** Description: ** Sql2005分页存储过程 ** Finish DateTime: ** 2009/1/3 ** Example: ** WEB_PageView @Tablename = 'Table1', @Returnfields = '*', ** @PageSize = 2, @PageIndex = 1, @Where = '', ** @OrderBy=N'ORDER BY id desc' **********************************************************************************/
IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'[dbo].[PagedProc]') and OBJECTPROPERTY(ID, N'IsProcedure') = 1)DROP PROCEDURE [dbo].[PagedProc]GO
SET QUOTED_IDENTIFIER ONGO
SET ANSI_NULLS ON GO
CREATE PROCEDURE dbo.PagedProc @TableName NVARCHAR(200), -- 表名 @ReturnFields NVARCHAR(1000) = '*', -- 需要返回的列 @PageSize INT = 10, -- 每页记录数 @PageIndex INT = 1, -- 当前页码 @Where NVARCHAR(1000) = '', -- 查询条件 @OrderBy NVARCHAR(1000), -- 排序字段名 最好为唯一主键 @PageCount INT OUTPUT, -- 页码总数 @RecordCount INT OUTPUT -- 记录总数
WITH ENCRYPTION AS
--设置属性SET NOCOUNT ON
-- 变量定义DECLARE @TotalRecord INTDECLARE @TotalPage INTDECLARE @CurrentPageSize INTDECLARE @TotalRecordForPageIndex INT
BEGIN IF @Where IS NULL SET @Where=N'' -- 记录总数 DECLARE @countSql NVARCHAR(4000) IF @RecordCount IS NULL BEGIN SET @countSql='SELECT @TotalRecord=Count(*) From '+@TableName+' '+@Where EXECUTE sp_executesql @countSql,N'@TotalRecord int out',@TotalRecord OUT END ELSE BEGIN SET @TotalRecord=@RecordCount END SET @RecordCount=@TotalRecord SET @TotalPage=(@TotalRecord-1)/@PageSize+1 SET @CurrentPageSize=(@PageIndex-1)*@PageSize
-- 返回总页数和总记录数 SET @PageCount=@TotalPage SET @RecordCount=@TotalRecord -- 返回记录 SET @TotalRecordForPageIndex=@PageIndex*@PageSize EXEC ('SELECT * FROM (SELECT TOP '+@TotalRecordForPageIndex+' '+@ReturnFields+', ROW_NUMBER() OVER ('+@OrderBy+') AS PageView_RowNo FROM '+@TableName+ ' ' + @Where +' ) AS TempPageViewTable WHERE TempPageViewTable.PageView_RowNo > '+@CurrentPageSize) ENDRETURN 0GO