分页存储过程

CREATE PROCEDURE [dbo].[sp_Page_Select]
 @TableName   NVARCHAR(200),    --表名
    @SelectList         NVARCHAR(500),    --欲选择字段列表
    @SearchCondition    NVARCHAR(2000),      --查询条件
    @OrderExpression    NVARCHAR(500),      --排序表达式
    @PageIndex          INT = 0,           --页号,从0开始
    @PageSize           INT = 10,          --页大小
 @TotalCount   INT OUTPUT,     --记录总数
 @TotalPages   INT OUTPUT     --页面总数
AS
BEGIN
 --关闭返回计数
    SET NOCOUNT ON
 --设置Where条件
    SET @SearchCondition = ISNULL(@SearchCondition,'')
    SET @SearchCondition = LTRIM(RTRIM(@SearchCondition))
 IF ltrim(rtrim(@SearchCondition)) <> ''
    BEGIN
        SET @SearchCondition = N' WHERE ' + @SearchCondition
    END
 --获得记录总数
 DECLARE @SQL NVARCHAR(2000)
 SET @sql=N'SELECT @TotalCount=COUNT(*)'
        +N' FROM '+@tablename
  +' '+@SearchCondition
    EXEC sp_executesql @sql,N'@TotalCount int OUTPUT',@TotalCount OUTPUT
-- set @TotalCount = 10
 IF @TotalCount % @PageSize = 0
   BEGIN
    SET @TotalPages = @TotalCount / @PageSize
   END
 ELSE
  BEGIN
    SET @TotalPages = @TotalCount / @PageSize + 1
  END
 --设置选择字段
    IF @SelectList IS NULL OR LTRIM(RTRIM(@SelectList)) = ''
    BEGIN
        SET @SelectList = '*'
    END
 
 --设置Oreder by 条件
    SET @OrderExpression = LTRIM(RTRIM(@OrderExpression))
    SET @OrderExpression = ISNULL(@OrderExpression,'')
 IF @OrderExpression <> ''
    BEGIN
        SET @OrderExpression = 'ORDER BY ' + @OrderExpression
    END
  
 --设置分页数
    IF @PageIndex IS NULL OR @PageIndex < 0
    BEGIN
        SET @PageIndex = 0
    END   
   
    SET @PageIndex = @PageIndex + 1
  
    IF @PageSize IS NULL OR @PageSize < 1
    BEGIN
        SET @PageSize = 5
    END
 --构造查询语句
    DECLARE @SqlQuery NVARCHAR(2000)
    --SET @SqlQuery='SELECT ' + @SelectList + ',RowNumber
    --FROM
    --    (SELECT ' + @SelectList + ',ROW_NUMBER() OVER( '+ @OrderExpression +') AS RowNumber
    --      FROM  '+ @TableName +' '+ @SearchCondition +') AS RowNumberTableSource
    --WHERE RowNumber BETWEEN ' + CAST(((@PageIndex - 1)* @PageSize+1) AS NVARCHAR)
    --+ ' AND ' +
    --CAST((@PageIndex * @PageSize) AS NVARCHAR)
    --Edit by xlli at 20100109 适应多表关联查询
      SET @SqlQuery='SELECT *
    FROM
        (SELECT ' + @SelectList + ',ROW_NUMBER() OVER( '+ @OrderExpression +') AS RowNumber
          FROM  '+ @TableName +' '+ @SearchCondition +') AS RowNumberTableSource
    WHERE RowNumber BETWEEN ' + CAST(((@PageIndex - 1)* @PageSize+1) AS NVARCHAR)
    + ' AND ' +
    CAST((@PageIndex * @PageSize) AS NVARCHAR)
   
 --执行查询
    EXECUTE sp_executesql @SqlQuery
 --开启返回计数
    SET NOCOUNT OFF
END