分页存储过程

原文：http://www.cnblogs.com/sealzrt/archive/2009/07/20/1527225.html

--====分页存储过程===-------
if exists(select * from sysobjects where type='p' and name='Proc_GetPagingList')
begin
drop procedure Proc_GetPagingList
end

GO 

Create Procedure Proc_GetPagingList
(
@Table nvarchar(1000),----------------表名
@Field nvarchar(1000) = '*',----------字段
@Where  nvarchar(500) = NULL,---------Where条件 不用加 where
@OrderBy nvarchar(500)= NULL,---------排序字段  不用加 order by
@PrimaryKeyField nvarchar(50),--------主键 不能为空 
@PageNumber int = 1,------------------当前页
@PageSize int = 10 -------------------每页显示的记录数
)
AS
BEGIN

    DECLARE @strWhere nvarchar(500)                   
    IF @Where IS NOT NULL AND @Where != ''             
    BEGIN
        SET @strWhere = ' WHERE ' + @Where + ' '
    END
    ELSE
    BEGIN
        SET @strWhere = ''
    END

    DECLARE @strOrderBy nvarchar(500)                  
    IF @OrderBy IS NULL OR @OrderBy = ''               
    BEGIN
        SET @strOrderBy = ' ORDER BY ' + @PrimaryKeyField + ' DESC '
    END
    ELSE
    BEGIN
        SET @strOrderBy = ' ORDER BY ' + @OrderBy
    END

    DECLARE @strSql nvarchar(max)   --Sql 语句
    DECLARE @totalRecord int --------保存记录总数 直接可以在结果里 Conver.ToInt32(Table.Rows[0]["TotalRecord"].ToString()) 可以取得 记录总数
    SET @strSql='SELECT @totalRecord = Count(*) FROM '+ @Table + @strWhere
    EXEC sp_executesql @strSql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT

--------  第一页

    IF @PageNumber < 1                                 
    BEGIN   
        SET @PageNumber = 1
    END

    IF @PageNumber = 1                          
    BEGIN
        SET @strSql = 'SELECT TOP ' + str(@PageSize) +  ' @totalRecord as TotalRecord,' + @Field + ' FROM ' + @Table + 
                       @strWhere + @strOrderBy
        EXEC sp_executesql @strSql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT
        RETURN
    END
    ---------  其他页
    DECLARE @STARTID nvarchar(50)
    DECLARE @ENDID nvarchar(50)
    SET @STARTID = convert(nvarchar(50),(@PageNumber - 1) * @PageSize + 1)
    SET @ENDID = convert(nvarchar(50),@PageNumber * @PageSize)
    SET @strSql = 'WITH MYTABLE AS (SELECT ROW_NUMBER() OVER (' + @strOrderBy + ')
                   AS RowNumber,@totalRecord as TotalRecord,' + @Field + ' FROM '+ @Table +  @strWhere + ') 
                   SELECT * FROM MYTABLE 
                   WHERE RowNumber BETWEEN ' + @STARTID + ' AND ' + @ENDID
    EXEC sp_executesql @strSql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT 

END