sqlserver2000分页

alter PROCEDURE pagination

       @tblName varchar(255), -- 表名

       @strGetFields varchar(1000) = '*', -- 需要返回的列

       @fldName varchar(255)='', -- 排序的字段名

       @PageSize int , -- 页尺寸

       @PageIndex int, -- 页码

       @doCount bit , -- 返回记录总数, 非 0 值则返回

       @OrderType bit , -- 设置排序类型, 非 0 值则降序

       @strWhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where)

       --@result varchar(1000) output

AS

       declare @strSQL varchar(5000) -- 主语句

       declare @strTmp varchar(110) -- 临时变量

       declare @strOrder varchar(400) -- 排序类型

if @doCount!=0

begin

       if @strWhere!=''

              set @strSQL='select count(*) as Total from [' + @tblName + '] where '+@strWhere

       else

              set @strSQL = 'select count(*) as Total from [' + @tblName + ']'

end

--以上代码的意思是如果@doCount传递过来的不是0，就执行总数统计。以下的所有代码都是@doCount为0的情况

else

begin

       --如果@OrderType不是0，就执行降序，这句很重要！

       if @OrderType!=0

       begin

              set @strTmp='<(select min'

              set @strOrder='order by ['+@fldName+'] desc'

       end

       else

       begin

              set @strTmp='>(select max'

              set @strOrder='order by ['+@fldName+'] asc'

       end

       --如果是第一页就执行以下代码，这样会加快执行速度

       if @PageIndex=1

       begin

              if @strWhere!=''

                     set @strSQL='select top '+str(@PageSize)+' '+@strGetFields+' from ['+@tblName+'] where '+@strWhere+' '+@strOrder

              else

                     set @strSQL='select top'+str(@PageSize)+' '+@strGetFields+' from ['+@tblName+']'+@strOrder

       end

       else

       begin

              --以下代码赋予了@strSQL以真正执行的SQL代码

              set @strSQL='select top '+str(@PageSize)+' '+@strGetFields+' from ['+@tblName+'] where ['+@fldName+']'+@strTmp+'(['+@fldName+']) from (select top '

+str((@PageIndex-1)*@PageSize) + ' ['+ @fldName + '] from ['+@tblName+']'+@strOrder+') as tblTmp)'+@strOrder

             

              if @strWhere!=''

                     set @strSQL='select top '+str(@PageSize)+' '+@strGetFields+' from ['+@tblName+'] where ['+@fldName+']'+@strTmp+'(['+@fldName+']) from (select top '

+str((@PageIndex-1)*@PageSize) +' ['+@fldName+'] from ['+@tblName+'] where '+@strWhere+' '+@strOrder+') as tblTmp) and '+@strWhere+' '+@strOrder

       end

/*

 

 

 

*/

--set @result=@strSQL

end

       exec(@strSQL)

GO

        protected void Page_Load(object sender, EventArgs e)

        {

            if (!IsPostBack)

            {

                DataTable dt=GetCustomersData("Customers","CustomerID,CompanyName","CustomerID",10,2,0,1,"").Tables[0];

                GridView1.DataSource = dt;

                GridView1.DataBind();

            }

        }

        private DataSet GetCustomersData(string tblName, string strGetFields, string fldName, int PageSize, int PageIndex, int doCount, int OrderType, string strWhere)

        {

            string conns = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ToString();

            string connString = ConfigurationSettings.AppSettings["NorthwindConnectionString"];

            SqlConnection conn = new SqlConnection(connString);

            SqlCommand comm = new SqlCommand("pagination",conn);

            comm.Parameters.Add(new SqlParameter("@tblName",SqlDbType.VarChar));//表名

            comm.Parameters[0].Value = tblName;

            comm.Parameters.Add(new SqlParameter("@strGetFields", SqlDbType.VarChar));//返回的列

            comm.Parameters[1].Value = strGetFields;

            comm.Parameters.Add(new SqlParameter("@fldName", SqlDbType.VarChar));//排序的字段名

            comm.Parameters[2].Value = fldName;

            comm.Parameters.Add(new SqlParameter("@PageSize", SqlDbType.Int));//页尺寸

            comm.Parameters[3].Value = PageSize;

            comm.Parameters.Add(new SqlParameter("@PageIndex", SqlDbType.Int));//页码

            comm.Parameters[4].Value = PageIndex;

            comm.Parameters.Add(new SqlParameter("@doCount", SqlDbType.Int));//是否返回记录总数，0为不返回，1为返回

            comm.Parameters[5].Value = doCount;

            comm.Parameters.Add(new SqlParameter("@OrderType", SqlDbType.Int));//设置排序类型，0为升序，非0为降序

            comm.Parameters[6].Value = OrderType;

            comm.Parameters.Add(new SqlParameter("@strWhere", SqlDbType.VarChar));//where语句

            comm.Parameters[7].Value = strWhere;

            comm.CommandType = CommandType.StoredProcedure;

            SqlDataAdapter dataAdapter = new SqlDataAdapter(comm);

            DataSet ds = new DataSet();

            dataAdapter.Fill(ds);

            return ds;

        }

declare @answer varchar(1000)

exec pagination 'Customers','CustomerID,CompanyName','CustomerID',10,2,0,1,'CustomerID>''A''',@answer output

select @answer

----------------------------------------------------------------------

 select top 10 CustomerID,CompanyName

from [Customers]

where [CustomerID]>

       (

              select max([CustomerID])

              from (

                     select top 10 [CustomerID]

                     from [Customers]

                     order by [CustomerID] asc

                    ) as tblTmp

       )

order by [CustomerID] asc

 

select top 10 CustomerID,CompanyName

from [Customers]

where [CustomerID]<

       (

              select min([CustomerID])

              from (

                     select top 10 [CustomerID]

                     from [Customers]

                     order by [CustomerID] desc

                   ) as tblTmp

)order by [CustomerID] desc

 

select top 10 CustomerID,CompanyName

from [Customers]

where [CustomerID]<

       (

              select min([CustomerID])

               from

              (

                     select top 10 [CustomerID]

                     from [Customers] where CustomerID>'A' order by [CustomerID] desc) as tblTmp) and CustomerID>'A' order by [Cu

 

 

 

 

 

 

 

转载于:https://www.cnblogs.com/yidianfeng/archive/2008/12/27/1363668.html