高效存储过程

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Pagination]
(
    @TableNames varchar(200), --表名(支持多表)
    @FieldStr varchar(4000), --字段名(全部字段为*)
    @SqlWhere varchar(4000), --条件语句(不用加where)
    @GroupBy varchar(4000), --Group语句(不用加Group By)
    @OrderBy varchar(4000), --排序字段(必须!支持多字段，不用加Order By)
    @PageSize int, --每页多少条记录
    @PageIndex int, --指定当前为第几页
    @TotalPage int output, --返回总页数
    @TotalRecord int output --返回总条数
    --with encryption --加密时使用
)
As
Begin
-- Begin Transaction
Declare @Sql nvarchar(4000)
--计算总记录数
set @Sql = 'select @TotalRecord = count(1) from ' + @TableNames
If (@SqlWhere !='' or @SqlWhere is not NULL)
set @Sql = @Sql + ' where ' + @SqlWhere
Exec sp_executesql @Sql,N'@TotalRecord int output',@TotalRecord output --计算总记录数

--计算总页数
set @TotalPage=CEILING((@TotalRecord+0.0)/@PageSize)
--处理页数超出范围情况
if @PageIndex <= 0
Set @PageIndex = 1

if @PageIndex > @TotalPage
Set @PageIndex = @TotalPage
--处理开始点和结束点
Declare @StartRecord int
Declare @EndRecord int

set @StartRecord = (@pageIndex-1)*@PageSize + 1
set @EndRecord = @StartRecord + @PageSize - 1
--合成sql条件
Declare @TempStr varchar(4000)
If (@SqlWhere != '' or @SqlWhere is not NULL)
set @TempStr = ' where ' + @SqlWhere
If (@GroupBy != '' or @GroupBy is not NULL)
set @TempStr = @TempStr + ' Group By ' + @GroupBy
--如果是第一页
If (@PageIndex = 1)
Begin
set @Sql = 'select top ' + Convert(varchar(50),@PageSize) + ' row_number() over(order by ' + @OrderBy + ') as rowId,' + @FieldStr + ' from ' + @TableNames
If (@TempStr !='' or @TempStr is not NULL)
set @Sql = @Sql + ' ' + @TempStr
End
Else
Begin
set @Sql = 'select row_number() over(order by ' + @OrderBy + ') as rowId,' + @FieldStr + ' from ' + @TableNames
If (@TempStr !='' or @TempStr is not NULL)
set @Sql = @Sql + ' ' + @TempStr
set @Sql = 'Select * from (' + @Sql + ') as TempTable where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' + Convert(varchar(50),@EndRecord)
End
--执行查询
Exec(@Sql)
-- If @@Error <> 0
-- Begin
-- RollBack Transaction
-- Return -1
-- End
-- Else
-- Commit Transaction
End

------------------------------------------使用方法如下-------------------------------------------
---------------------单表查询----------------------
public DataTable GetPage......{
......
SqlParameter[] par_array = new SqlParameter[]
            {
                new SqlParameter("@TableNames",dbTable),//表名
                new SqlParameter("@FieldStr",Fields),//字段
                new SqlParameter("@SqlWhere",sFilter),//where条件
                new SqlParameter("@GroupBy",DBNull.Value),//GroupBy
                new SqlParameter("@OrderBy",OrderStr),//排序
                new SqlParameter("@PageSize",PageSize),//每页记录数
                new SqlParameter("@PageIndex",PageIndex),//当前页码
                new SqlParameter("@TotalPage",SqlDbType.Int),//总页数
                new SqlParameter("@TotalRecord",SqlDbType.Int)//总记录
            };
            par_array[7].Direction = ParameterDirection.Output;
            par_array[8].Direction = ParameterDirection.Output;
            DataTable dt = DbHelper.ExecuteTable(CommandType.StoredProcedure, "Pagination", par_array);
            RecordCount = Convert.ToInt32(par_array[8].Value);
            PageCount = Convert.ToInt32(par_array[7].Value);
            return dt;
}
-------------多表查询-----------
/// <summary>
        /// 新闻列表-利用存储过程分页 write by gibil
        /// </summary>
        public DataTable GetPage(string SpecialID, string Editor, string ClassID, string sKeywrd, string DdlKwdType, string sChooses, string SiteID, int PageIndex, int PageSize, out int RecordCount, out int PageCount, params SQLConditionInfo[] SqlCondition)
        {
            string sFilter = " a.isRecyle=0";
            if (ClassID != null && ClassID != "")
                sFilter += " and a.ClassID='" + ClassID + "'";
            if (SpecialID != null && SpecialID != "")
                sFilter += " and a.NewsID In (Select NewsID From " + Pre + "special_news Where SpecialID='" + SpecialID + "') ";
            string AllFields = "a.Id,a.NewsID,a.NewsType,a.TitleColor,a.TitleITF,a.TitleBTF,a.Author,a.DataLib,a.OrderID,a.NewsTitle,a.ishtml,a.Editor,a.Click,a.isConstr,a.ClassID,a.isLock,a.NewsProperty,a.CheckStat,a.URLaddress,b.UserName,c.ClassCName";
            string TabelNames = "CI_News a left join CI_sys_User b on a.Editor=b.UserName left join CI_News_Class c on a.ClassID=c.ClassID ";
            string OrderFields = " a.OrderID desc,a.Id desc";
            SqlParameter[] par_array = new SqlParameter[]
            {
                new SqlParameter("@TableNames",TabelNames),//表名
                new SqlParameter("@FieldStr",AllFields),//字段
                new SqlParameter("@SqlWhere",sFilter),//where条件
                new SqlParameter("@GroupBy",DBNull.Value),//GroupBy
                new SqlParameter("@OrderBy",OrderFields),//排序
                new SqlParameter("@PageSize",PageSize),//每页记录数
                new SqlParameter("@PageIndex",PageIndex),//当前页码
                new SqlParameter("@TotalPage",SqlDbType.Int),//总页数
                new SqlParameter("@TotalRecord",SqlDbType.Int)//总记录
            };
            par_array[7].Direction = ParameterDirection.Output;
            par_array[8].Direction = ParameterDirection.Output;
            DataTable dt = DbHelper.ExecuteTable(CommandType.StoredProcedure, "Pagination", par_array);
            RecordCount = Convert.ToInt32(par_array[8].Value);
            PageCount = Convert.ToInt32(par_array[7].Value);
            return dt;
        }