MSSQL 存储过程分页

create
 PROCEDURE [dbo].[PageRecord]
    @Sql varchar(max),
    @OrderBy varchar(50),
    @PageIndex int,
    @PageSize int
AS
BEGIN
    DECLARE @ExecSql varchar(max)
    SET @ExecSql='SELECT * FROM (' 
    + 'SELECT *,Row_Number() Over(' + @OrderBy + ') as RowNumber FROM ('+
    @Sql + ') as a ) as b' + 
    ' WHERE b.RowNumber BETWEEN ' + convert(varchar(50),(@PageIndex-1)*@PageSize+1) + ' and ' + convert(varchar(50),(@PageIndex-1)*@PageSize+@PageSize)
    EXEC(@ExecSql)
    print @ExecSql
END

使用方式：

      List<SqlParameter> splist = new List<SqlParameter>();
            splist.Add(new SqlParameter("@Sql", sql));
            splist.Add(new SqlParameter("@OrderBy", "order by TranceTime  "));
            if (_page.PageSize <= 0)
            {
                splist.Add(new SqlParameter("@PageIndex", 1));
                splist.Add(new SqlParameter("@PageSize", "200000"));
            }
            else
            {
                splist.Add(new SqlParameter("@PageIndex", _page.PageIndex));
                splist.Add(new SqlParameter("@PageSize", _page.PageSize));
            }

            DataTable dt = null;
            if (_page.PageSize <= 0)
            {
                dt = da.GetDataTable(sql);
            }
            else
            {
                dt = da.GetDataTable("PageRecord", CommandType.StoredProcedure, splist.ToArray());
            }