公司购物网分页存储过程（留着以备后用）

USE [Ezagoo]GO/****** 对象:  StoredProcedure [dbo].[AspNetPagerPROC]    脚本日期: 09/21/2012 11:52:59 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO

 

CREATE procedure [dbo].[AspNetPagerPROC](@PAGESIZE int,            --每页行数    @CURRPAGE int,            --当前页数@TABLENAME nvarchar(4000), --表名@KEYID nvarchar(70),      --主键@WHERE nvarchar(4000),     --条件@SORT nvarchar(80),       --排序@COLS nvarchar(4000),      --列名@COUNT  nvarchar(50),     --针对获得总条数时需要的特殊条件，一般为*@ROWS int output          --总行数)asbeginset nocount ondeclare @befROWS int,@sql nvarchar(4000)set @befROWS=@PAGESIZE*(@CURRPAGE-1)set @sql='SELECT TOP '+CAST(@PAGESIZE as nvarchar(50))+' '+@COLS+' FROM '+@TABLENAME+' WHERE '+@WHERE+' AND '+@KEYID+' NOT IN (SELECT TOP '+CAST(@befROWS as nvarchar(50))+' '+@KEYID+' FROM '+@TABLENAME+' WHERE '+@WHERE+' '+@SORT+' ) '+@SORT+''EXEC (@sql)print @sql-------总条数------set @sql=N'SELECT @ROWS=COUNT('+@COUNT+') FROM '+@TABLENAME+' WHERE '+@WHERE+' '+''exec sp_executesql @sql,N'@ROWS int out',@ROWS outselect  @ROWSreturn @ROWSend

 

 

--配套使用方法

 

        /// <summary>        /// 获取订单列表        /// </summary>        /// <param name="PageSize"></param>        /// <param name="CurrPage"></param>                /// <param name="ListCount"></param>        /// <returns></returns>        public static DataTable GetOrdersListPager3(string strWhere, string orderBy, int pageSize, int currPage, ref int listCount)        {            if (strWhere == "")            {                strWhere = "1=1";            }

            if (orderBy == "")            {                orderBy = " ORDER BY OrderID ";            }            DataTable dt = null;            SqlParameter[] parameters = new SqlParameter[9];            parameters[0] = new SqlParameter("@PAGESIZE", SqlDbType.Int);            parameters[0].Value = pageSize;

            parameters[1] = new SqlParameter("@CURRPAGE", SqlDbType.Int);            parameters[1].Value = currPage;

            parameters[2] = new SqlParameter("@TABLENAME", SqlDbType.NVarChar, 50);            parameters[2].Value = "V_ChargeBack";

            parameters[3] = new SqlParameter("@KEYID", SqlDbType.NVarChar, 50);            parameters[3].Value = "OrderID";

            parameters[4] = new SqlParameter("@WHERE", SqlDbType.NVarChar, 4000);            parameters[4].Value = strWhere;

            parameters[5] = new SqlParameter("@SORT", SqlDbType.NVarChar, 50);            parameters[5].Value = orderBy;

            parameters[6] = new SqlParameter("@COLS", SqlDbType.NVarChar, 200);            parameters[6].Value = "*";

            parameters[7] = new SqlParameter("@COUNT", SqlDbType.NVarChar, 50);            parameters[7].Value = "*";

            parameters[8] = new SqlParameter("@ROWS", SqlDbType.Int);            parameters[8].Direction = ParameterDirection.Output;

            try            {                dt = EzagooDB.GetDataTablePager(out listCount, parameters);            }            catch (Exception ex)            {                throw ex;            }            return dt;        }

 

 

 

转载于:https://www.cnblogs.com/shyhoo/archive/2012/09/21/2696674.html

相关资源：数据结构—成绩单生成器