sql server分页存储过程

/********************************************************************************* * 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 ON GO
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 INT DECLARE @TotalPage INT DECLARE @CurrentPageSize INT DECLARE @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)      END RETURN 0 GO             

sql server分页存储过程

标签：int   arch   cut   主键   ota   over   from   record   ***