• 企业400电话
  • 微网小程序
  • AI电话机器人
  • 电商代运营
  • 全 部 栏 目

    企业400电话 网络优化推广 AI电话机器人 呼叫中心 网站建设 商标✡知产 微网小程序 电商运营 彩铃•短信 增值拓展业务
    分页存储过程(一)使用sql2005的新函数构造分页存储过程
    其实在很多时候设计的度还是要把握的,不至于让自己陷入【非要把它设计成万能的分页存储过程】的怪圈中才是最重要的,因为我们还要留出时间还解决其他的很多问题,个人认为适度就可以了,留出一定的空间。也因为万能是不存在的,万物在一定的范畴之内都是合理的,出了范畴可能就没有合理的了。

             分页存储过程大致有下列几种

    1、 利用Not in select top

     

    2、 利用id大于多少和select top

    3、 利用sql中的游标

    4、临时表

     可以参看网上的以下链接

       C#中常用的分页存储过程小结
    http://read.newbooks.com.cn/info/174545.html

             2005中我们的选择就多了,可以利用新语法CTE(公用表表达式),关于CTE的介绍大家可以参看博客园中一位仁兄的系列教程

             http://www.cnblogs.com/nokiaguy/archive/2009/01/31/1381562.html

             或者干脆上微软的官网

    http://msdn.microsoft.com/zh-cn/library/ms190766(SQL.90).aspx

    查看具体内容。

             除此之外还可以利用在2005中新增的一些函数,分别是:row_number(),rank,dense_rank,ntile,这些新函数是您可以有效的分析数据以及向查询饿结果行提供排序值。您可能发现这些新函数有用的典型方案包括:将连续整数分配给结果行,以便进行表示、分页、计分和绘制直方图。

             详细介绍参见下列链接

      http://blog.csdn.net/htl258/archive/2009/03/20/4006717.aspx

             我这里主要使用的就是row_number()结合新语法CTE,先贴上我的存储过程。设计,开发,测试存储过程和相关的C#代码就花费我两天的时间,不过后面的相似界面就很快了,一上午就可以搞两个分页显示的页面,就算是复杂的查询,一上午也可以搞定。

      下面的存储过程没有将总页数和总条目数返回,如果你有兴趣,可以自己加上,可以参看 C#中常用的分页存储过程小结中的下列部分

      Declare @sql nvarchar(4000);
    Declare @totalRecord int;
    --计算总记录数
    if (@SqlWhere ='''' or @SqlWhere='' or @sqlWhere is NULL)
    set @sql = 'select @totalRecord = count(*) from ' + @TableName
    else
    set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' where ' + @sqlWhere
    EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数

    --计算总页数

    select @TotalPage=@totalRecord --CEILING((@totalRecord+0.0)/@PageSize)

    存储过程SQL如下,支持不定列,不定条件,多表联合,排序任意

    复制代码 代码如下:

    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    go
    --Declare @sql nvarchar(4000);
    --Declare @totalRecord int;
    ----计算总记录数
    --if (@SqlWhere ='''' or @SqlWhere='' or @sqlWhere is NULL)
    --set @sql = 'select @totalRecord = count(*) from ' + @TableName
    --else
    --set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' where ' + @sqlWhere
    --EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数
    --
    ----计算总页数
    --
    --select @TotalPage=@totalRecord --CEILING((@totalRecord+0.0)/@PageSize)
    -- =============================================
    -- Author: shiwenbin
    -- MSN:    jorden008@hotmail.com
    -- Email:   jorden008@163.com
    -- Create date: 2009-10-20
    -- Description: 分页存储过程,根据传递的参数返回分页的结果
    -- Parameters:
    -- =============================================
    ALTER PROCEDURE [dbo].[Proc_GetDataPaged]
    -- Add the parameters for the stored procedure here
    @StrSelect varchar(max)=null, --欲显示的列(多列用逗号分开),例如:id,name
    @StrFrom varchar(max)= null, --表名称,或者是表连接字符串,多表连接例如:student as s inner join dwinfo as dw on s.dwbh=dw.bh
    @StrWhere varchar(max)=null, --查询条件,''代表没有条件,单条件或者多条件,多条件例如:name='啊' and id=10
    @StrOrder varchar(max) =null, --排序列(多个排序列用逗号分开),例如:id desc,name as
    --@PageCount int output, --总页数
    @ItemCount bigint output, --总记录数
    @PageSize int =50, --每页显示条数
    @BeginIndex int=1,--记录开始数
    @DoCount bit =0 --是否统计总数,为0不统计,为1统计
    -- @PageIndex int =1 --当前页
    --@ClassCode char(10) =null, --单位编号(班级编号)
    AS
    BEGIN
    SET NOCOUNT ON;
    Declare @sql nvarchar(4000);
    Declare @totalRecord int;
    --计算总记录数
    if (@StrWhere ='''' or @StrWhere='' or @StrWhere is NULL)
    set @sql = 'select @totalRecord = count(*) from ' + @StrFrom
    else
    set @sql = 'select @totalRecord = count(*) from ' + @StrFrom + ' where ' + @StrWhere
    EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@ItemCount OUTPUT--计算总记录数
    declare @SqlQuery varchar(max)
    -- if(@PageIndex=1)
    if(@BeginIndex=1 or @BeginIndex=0 or @BeginIndex 0)
    begin
    if(@StrWhere is null)--if(@StrWhere='')
    set @SqlQuery='select top '+convert(varchar,@PageSize)
    + ' row_number() over(order by '+@StrOrder+' ) as RowNumber,'+@StrSelect+
    ' from '+@StrFrom;
    else
    --set @sql='select top @PageSize * from @TableName order by id desc';
    --select top @PageSize * from @TableName order by id desc;
    set @SqlQuery='select top '+convert(varchar,@PageSize)
    + ' row_number() over(order by '+@StrOrder+' ) as RowNumber,'+@StrSelect+' from '+@StrFrom+' where '+@StrWhere;
    --exec (@SqlQuery)
    -- @SqlQuery
    end
    else
    begin
    if(@StrWhere is null)--if(@StrWhere='')
    begin
    set @SqlQuery='with cte as (
    select row_number() over(order by '+@StrOrder+' ) as RowNumber,'+@StrSelect+' from '+@StrFrom+'
    )
    select * from cte where RowNumber between '+
    --convert(varchar,((@PageIndex-1)*@PageSize)+1)+' and '+
    --
    -- convert(varchar,@PageIndex*@PageSize)
    convert(varchar,@BeginIndex)+' and '+
    convert(varchar,@BeginIndex+@PageSize)
    --print @SqlQuery
    end
    else
    begin
    set @SqlQuery='with cte as (
    select row_number() over(order by '+@StrOrder+' ) as RowNumber,'+@StrSelect+' from '+@StrFrom+' where '+@StrWhere+'
    )
    select * from cte where RowNumber between '+
    --convert(varchar,((@PageIndex-1)*@PageSize)+1)+' and '+
    --
    -- convert(varchar,@PageIndex*@PageSize)
    convert(varchar,@BeginIndex)+' and '+
    convert(varchar,@BeginIndex+@PageSize)
    --print @SqlQuery
    end
    end
    --set @SqlQuery=@SqlQuery+';select @ItemCount =count(*) from '+@TableName
    --set @PageCount=@ItemCount/@PageSize
    --print '共'+@PageConut+'页'+@ItemCount+'条'
    --print @ItemCount
    print @SqlQuery
    exec (@SqlQuery)
    END

    c#相关代码的数据库访问使用的是微软的企业库 V4.1

     Enterprise Library 4.1 下载地址:

      http://www.microsoft.com/downloads/details.aspx?FamilyId=1643758B-2986-47F7-B529-3E41584B6CE5displaylang=en

    示例代码,前台页面,前台为用户控件

    复制代码 代码如下:

    %@ Control Language="C#" AutoEventWireup="true" CodeBehind="StudentDetailsTable.ascx.cs" Inherits="Kimbanx.UCS.ForeignStudentAdmin.UserControl.UserControl.StudentDetailsTable" %>
    link href="../css/core.css" rel="stylesheet" type="text/css" />
    table class="StudentPagingTablePanel">
    tr>
    td> 单位:asp:Label ID="lblClassName" runat="server" Text="Label">/asp:Label>/td>
    td>级别:asp:Label ID="lblClassLevel" runat="server" Text="Label">/asp:Label>级节点/td>
    /tr>
    tr>
    td>该单位共有asp:Label ID="lblStudentType" runat="server" Text="Label">/asp:Label>学员
    asp:Label ID="lblStudentCount" runat="server" Text="Label">/asp:Label>人/td>
    td>每页显示asp:DropDownList ID="ddlPageSize" runat="server" AutoPostBack="True"
    onselectedindexchanged="ddlPageSize_SelectedIndexChanged">
    /asp:DropDownList>人nbsp;nbsp;共asp:Label ID="lblPageCount" runat="server" Text="Label">/asp:Label>页
    nbsp;nbsp;现为第asp:Label ID="lblPageIndex" runat="server" Text="Label">/asp:Label>页nbsp;nbsp;
    asp:LinkButton ID="firstPage" runat="server" CommandArgument="first"
    oncommand="LinkButton_Command">首页/asp:LinkButton>nbsp;nbsp;
    asp:LinkButton ID="nextPage" runat="server" CommandArgument="next"
    oncommand="LinkButton_Command">下一页/asp:LinkButton>nbsp;nbsp;
    asp:LinkButton ID="prevPage" runat="server" CommandArgument="prev"
    oncommand="LinkButton_Command">上一页/asp:LinkButton>nbsp;nbsp;
    asp:LinkButton ID="lastPage" runat="server" CommandArgument="last"
    oncommand="LinkButton_Command">末页/asp:LinkButton>
    /td>
    /tr>
    /table>
    br />
    asp:GridView ID="gvStudent" runat="server" AutoGenerateColumns="False"
    EmptyDataText="没有符合条件的数据">
    Columns>
    asp:TemplateField HeaderText="照片">
    ItemTemplate>
    asp:Image ID="Image1" CssClass="studentImage" ImageUrl =%# GetStudentImageUrl(Eval("zpadress")) %> runat="server" />
    /ItemTemplate>
    /asp:TemplateField>
    asp:TemplateField HeaderText="姓名(中英简)">
    ItemTemplate>
    asp:Label ID="Label1" runat="server" Text='%# Eval("xmjz") %>'>/asp:Label>
    br />
    asp:Label ID="Label2" runat="server" Text='%# Eval("xmjy") %>'>/asp:Label>
    /ItemTemplate>
    /asp:TemplateField>
    asp:BoundField DataField="jx" HeaderText="军衔" />
    asp:BoundField DataField="zw" HeaderText="职务" />
    asp:BoundField DataField="gj" HeaderText="国家" />
    asp:BoundField DataField="sjyqk" HeaderText="文化程度" />
    asp:BoundField DataField="zj" HeaderText="宗教" />
    asp:TemplateField HeaderText="出生/入伍">
    ItemTemplate>
    asp:Label ID="Label3" runat="server" Text='%# SetBirthDate(Eval("csrq")) %>'>/asp:Label>
    br />
    asp:Label ID="Label4" runat="server" Text='%# SetEnrollDate(Eval("rwrq")) %>'>/asp:Label>
    /ItemTemplate>
    /asp:TemplateField>
    asp:BoundField DataField="xzz" HeaderText="房间/楼号" />
    asp:TemplateField HeaderText="电话/小号">
    ItemTemplate>
    asp:Label ID="Label5" runat="server" Text='%# Eval("dhd") %>'>/asp:Label>
    br />
    asp:Label ID="Label6" runat="server" Text='%# Eval("dhx") %>'>/asp:Label>
    /ItemTemplate>
    /asp:TemplateField>
    asp:BoundField DataField="fcjp" HeaderText="返程机票" />
    asp:BoundField DataField="xh" HeaderText="学号" />
    /Columns>
    /asp:GridView>

    示例代码,后台代码
    复制代码 代码如下:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Text;
    using System.Data;
    using System.Data.Common;
    using Microsoft.Practices.EnterpriseLibrary.Common;
    using Microsoft.Practices.EnterpriseLibrary.Data;
    using Kimbanx.UCS.ForeignStudentAdmin.Model;
    using Kimbanx.UCS.ForeignStudentAdmin.Common;
    namespace Kimbanx.UCS.ForeignStudentAdmin.UserControl.UserControl
    {
    public partial class StudentDetailsTable : System.Web.UI.UserControl
    {
    private Database _db = DatabaseFactory.CreateDatabase();
    private DbCommand _command;
    private DbConnection _connection;
    private DataSet _ds;
    private string _classCode;
    private string _classFullName;
    private string _studentType;
    private string _studentCount;
    private string _queryStringWhere;
    private DataTable _studentTable;
    protected string SetBirthDate(object obj)
    {
    string result = string.Empty;
    string temp = obj.ToString();
    result = DateTime.Parse(temp).ToShortDateString();
    return result;
    }
    protected string SetEnrollDate(object obj)
    {
    string result = string.Empty;
    string temp = obj.ToString();
    result = DateTime.Parse(temp).ToShortDateString();
    return result;
    }
    protected void Filldata_dllPageSize()
    {
    for (int i = 1; i 100; i++)
    {
    ddlPageSize.Items.Add(i.ToString());
    }
    ddlPageSize.SelectedIndex = 14;
    }
    protected void InitSession()
    {
    //Session["PageSize"] = 0;
    Session["PageIndex"] = 1;
    Session["PageCount"] = int.Parse(_studentCount) / 15 + 1;
    }
    /// summary>
    /// 获取QueryString传递参数
    /// /summary>
    protected void GetQueryStringPara()
    {
    _classCode = Request.QueryString["dwbh"];
    _classFullName =HttpUtility.UrlDecode( Request.QueryString["dwmc"]);
    _studentCount = Request.QueryString["studentCount"];
    _studentType =HttpUtility.UrlDecode( Request.QueryString["studentType"]);
    _queryStringWhere = Request.QueryString["where"];
    }
    protected void SetLabelText()
    {
    this.lblClassName.Text = _classFullName;
    this.lblClassLevel.Text = GetClassInfo(_classCode).Level.ToString();
    this.lblStudentCount.Text = _studentCount;
    this.lblStudentType.Text = _studentType;
    }
    #region
    ///// summary>
    ///// 获取学员数据
    ///// /summary>
    ///// param name="strSelect">显示的字段/param>
    ///// param name="strFrom">用到的/param>
    /////param name="strWhere">查询条件/param>
    ///// param name="pageSize">每页显示条数/param>
    ///// param name="pageIndex">当前页/param>
    ///// returns>/returns>
    //protected DataTable GetStudentData(string strSelect,string strFrom,string strWhere,int pageSize,int pageIndex)
    //{
    // _command = _db.GetStoredProcCommand("StudentPaging");
    // _db.AddInParameter(_command, "StrSelect", DbType.String, "zpadress,xmjz,xmjy,jx,zw,gj,sjyqk,zj,csrq,rwrq,xzz,dhd,dhx,fcjp,hzh,xh");
    // _db.AddInParameter(_command, "StrFrom", DbType.String, "tx_xyzl");
    // _db.AddInParameter(_command, "StrWhere", DbType.String, strWhere );
    // _db.AddInParameter(_command, "StrOrder", DbType.String, "id");
    // _db.AddInParameter(_command, "PageSize", DbType.Int32, pageSize );
    // _db.AddInParameter(_command, "PageIndex", DbType.Int32,pageIndex );
    // _studentTable = _db.ExecuteDataSet(_command).Tables[0];
    // return _studentTable;
    //}
    #endregion
    protected string GetStudentImageUrl(object imageUrl)
    {
    string serverUrl = http://192.168.0.1/admin;
    string imageurl = string.Empty;
    if (!(imageUrl == null))
    {
    string temp = imageUrl.ToString().Trim();
    if (!string.IsNullOrEmpty(temp))
    { imageurl = string.Format("{0}{1}", serverUrl, temp.Substring(temp.IndexOf("/"))); }
    }
    return imageurl;
    }
    /// summary>
    /// 绑定分页之后的数据
    /// /summary>
    /// param name="pageSize">每页显示的数据量/param>
    /// param name="pageIndex">当前页/param>
    protected void BindStudentData(int pageSize, int pageIndex)
    {
    switch (_queryStringWhere)
    {
    case "jx":
    this.gvStudent.DataSource = Helper.StudentPagingResult(
    "zpadress,xmjz,xmjy,jx,zw,gj,sjyqk,zj,csrq,rwrq,xzz,dhd,dhx,fcjp,hzh,xh",
    "student",
    string.Format("dwbh='{0}' and jx='{1}'", _classCode, _studentType),
    "id",
    pageSize,
    pageIndex);
    this.gvStudent.DataBind();
    break;
    case "gj":
    this.gvStudent.DataSource = Helper.StudentPagingResult(
    "zpadress,xmjz,xmjy,jx,zw,gj,sjyqk,zj,csrq,rwrq,xzz,dhd,dhx,fcjp,hzh,xh",
    "student",
    string.Format("dwbh='{0}' and gj='{1}'", _classCode, _studentType),
    "id",
    pageSize,
    pageIndex);
    this.gvStudent.DataBind();
    break;
    case "allyear":
    this.gvStudent.DataSource = Helper.StudentPagingResult(
    "s.zpadress,s.xmjz,s.xmjy,s.jx,s.zw,s.gj,s.sjyqk,s.zj,s.csrq,s.rwrq,s.xzz,s.dhd,s.dhx,s.fcjp,s.hzh,s.xh",
    "student as s inner join class as dw on s.dwbh=dw.bh",
    string.Format(@"s.dwbh='{0}' and (dw.kxsj>=convert(datetime,'{1}'+'-01-01',120) and
    dw.kxsj=convert(datetime,'{1}'+'-12-31',120) or dw.bysj>=convert(datetime,'{1}'+'-01-01',120) and
    dw.bysj=convert(datetime,'{1}'+'-12-31',120)) ", _classCode, _studentType),
    "s.id",
    pageSize,
    pageIndex);
    this.gvStudent.DataBind();
    break;
    case "new":
    this.gvStudent.DataSource = Helper.StudentPagingResult(
    "s.zpadress,s.xmjz,s.xmjy,s.jx,s.zw,s.gj,s.sjyqk,s.zj,s.csrq,s.rwrq,s.xzz,s.dhd,s.dhx,s.fcjp,s.hzh,s.xh",
    "student as s inner join class as dw on s.dwbh=dw.bh",
    string.Format(@"s.dwbh='{0}' and (dw.kxsj>=convert(datetime,'{1}'+'-01-01',120) and
    dw.kxsj=convert(datetime,'{1}'+'-12-31',120)) ", _classCode, _studentType),
    "s.id",
    pageSize,
    pageIndex);
    this.gvStudent.DataBind();
    break;
    }
    }
    protected void Page_Load(object sender, EventArgs e)
    {
    if (UserAuthHelper.GetUserAuthType("1") == UserAuthEnum.Admin||
    UserAuthHelper.GetUserAuthType("2") == UserAuthEnum.CurrentStudentDetails)
    {
    GetQueryStringPara();
    SetLabelText();
    if (GetStudentCount() == 0)
    {
    StudentCountZero();
    return;
    }
    if (!IsPostBack)
    {
    Filldata_dllPageSize();
    SetPageIndex(1);
    SetPageCount();
    BindStudentData(GetPageSize(), GetPageIndex());
    }
    else
    {
    }
    }
    else
    {
    this.Controls.Add(new LiteralControl("您没有相应的权限,请联系管理员"));
    }
    }
    /// summary>
    /// 获取班级信息,班级全称,班级级别
    /// /summary>
    /// param name="classCode">班级编号/param>
    /// returns>/returns>
    protected ClassEntity GetClassInfo(string classCode)
    {
    ClassEntity entity = new ClassEntity();
    entity.Code = classCode;
    _command = _db.GetStoredProcCommand("ClassInfo");
    _db.AddInParameter(_command, "bh", DbType.String, classCode);
    using (IDataReader reader = _db.ExecuteReader(_command))
    {
    while (reader.Read())
    {
    entity.FullName = reader.GetString(1);
    entity.Level = reader.GetInt32(2);
    }
    }
    return entity;
    }
    #region Get and Set PageSize
    protected int GetPageSize()
    {
    return int.Parse(ddlPageSize.SelectedValue);
    }
    protected void SetPageSize(int pageSize)
    {
    this.ddlPageSize.Text = pageSize.ToString();
    }
    #endregion
    #region Get and Set PageIndex
    protected int GetPageIndex()
    {
    return int.Parse(this.lblPageIndex.Text.Trim());
    }
    protected void SetPageIndex(int pageIndex)
    {
    this.lblPageIndex.Text = pageIndex.ToString();
    }
    #endregion
    #region Get and Set PageCount
    protected int GetPageCount()
    {
    return int.Parse(this.lblPageCount.Text.Trim());
    }
    protected void SetPageCount()
    {
    int studentCount = GetStudentCount();
    int pageSize = GetPageSize();
    if (studentCount % pageSize == 0)
    {
    this.lblPageCount.Text = (studentCount / pageSize).ToString();
    }
    else
    {
    this.lblPageCount.Text = (studentCount / pageSize + 1).ToString();
    }
    }
    #endregion
    #region Get and Set StudentCount
    protected int GetStudentCount()
    {
    return int.Parse(this.lblStudentCount.Text.Trim());
    }
    protected void SetStudentCount(int studentCount)
    {
    this.lblStudentCount.Text = studentCount.ToString();
    }
    #endregion
    protected void StudentCountZero()
    {
    this.lblPageIndex.Text = "0";
    this.lblPageCount.Text = "0";
    }
    protected void LinkButton_Command(object sender, CommandEventArgs e)
    {
    if (GetStudentCount() == 0)
    {
    StudentCountZero();
    return;
    }
    int pageCount = GetPageCount();
    int pageIndex = GetPageIndex();
    int pageSize = GetPageSize();
    switch (e.CommandArgument.ToString())
    {
    case "first":
    if (pageIndex == 1) { }
    else
    {
    pageIndex = 1;
    SetPageIndex(pageIndex);
    pageSize = GetPageSize();
    SetPageCount();
    BindStudentData(pageSize, pageIndex);
    }
    break;
    case "next":
    if (pageCount == pageIndex pageIndex == 1)
    { }
    else if (pageIndex == 1 pageCount > pageIndex)
    {
    SetPageIndex(++pageIndex);
    pageSize = GetPageSize();
    SetPageCount();
    BindStudentData(pageSize, pageIndex);
    }
    else if (pageIndex > 1 pageCount == pageIndex)
    { }
    else
    {
    SetPageIndex(++pageIndex);
    pageSize = GetPageSize();
    SetPageCount();
    BindStudentData(pageSize, pageIndex);
    }
    break;
    case "prev":
    if (pageIndex == 1)
    { }
    else if (pageIndex == pageCount pageIndex > 1)
    {
    SetPageIndex(--pageIndex);
    pageSize = GetPageSize();
    SetPageCount();
    BindStudentData(pageSize, pageIndex);
    }
    else if (pageIndex == 2)
    {
    SetPageIndex(1);
    pageSize = GetPageSize();
    SetPageCount();
    BindStudentData(pageSize, pageIndex);
    }
    else
    {
    SetPageIndex(--pageIndex);
    pageSize = GetPageSize();
    SetPageCount();
    BindStudentData(pageSize, pageIndex);
    }
    break;
    case "last":
    if (pageCount == pageIndex)
    { }
    else
    {
    SetPageIndex(pageCount);
    pageIndex = GetPageIndex();
    SetPageCount();
    BindStudentData(pageSize, pageIndex);
    }
    break;
    default:
    SetPageIndex(1);
    pageSize = GetPageSize();
    SetPageCount();
    BindStudentData(pageSize, pageIndex);
    break;
    }
    }
    protected void ddlPageSize_SelectedIndexChanged(object sender, EventArgs e)
    {
    int pageIndex = GetPageIndex();
    int pageCount = GetPageCount();
    int pageSize = GetPageSize();
    pageIndex = 1;
    SetPageIndex(pageIndex);
    SetPageSize(int.Parse(((DropDownList)sender).SelectedValue));
    pageSize=GetPageSize();
    SetPageCount();
    BindStudentData(pageSize, pageIndex);
    }
    }
    }

    最后再贴一个圆友的通用存储过程,原文地址:通用存储过程分页(使用ROW_NUMBER()和不使用ROW_NUMBER()两种情况)性能分析
    复制代码 代码如下:

    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    go
    -- =============================================
    -- Author: jiangrod>
    -- Create date: 2010-03-03>
    -- Description: SQL2005及后续版本通用分页存储过程>调用方法: sp_Pager2005 'xtest','*','ORDER BY ID ASC','xname like ''%222name%''',2,20,0,0
    -- 适合从单个表查询数据
    -- =============================================
    ALTER PROCEDURE [dbo].[Proc_GetDataPaged2]
    @tblName varchar(255), -- 表名如:'xtest'
    @strGetFields varchar(1000) = '*', -- 需要返回的列如:'xname,xdemo'
    @strOrder varchar(255)='', -- 排序的字段名如:'order by id desc'
    @strWhere varchar(1500) = '', -- 查询条件(注意:不要加where)如:'xname like ''%222name%'''
    @beginIndex int=1, --开始记录位置
    --@pageIndex int = 1, -- 页码如:2
    @pageSize int = 50, -- 每页记录数如:20
    @recordCount int output, -- 记录总数
    @doCount bit=0 -- 非0则统计,为0则不统计(统计会影响效率)
    AS
    declare @strSQL varchar(5000)
    declare @strCount nvarchar(1000)
    --总记录条数
    if(@doCount!=0)
    begin
    if(@strWhere !='')
    begin
    set @strCount='set @num=(select count(1) from '+ @tblName + ' where '+@strWhere+' )'
    end
    else
    begin
    set @strCount='set @num=(select count(1) from '+ @tblName + ' )'
    end
    EXECUTE sp_executesql @strCount ,N'@num INT output',@RecordCount output
    end
    if @strWhere !=''
    begin
    set @strWhere=' where '+@strWhere
    end
    set @strSQL='SELECT * FROM (SELECT ROW_NUMBER() OVER ('+@strOrder+') AS ROWID,'
    set @strSQL=@strSQL+@strGetFields+' FROM ['+@tblName+'] '+@strWhere
    set @strSQL=@strSQL+') AS sp WHERE ROWID BETWEEN '+str(@beginIndex)
    set @strSQL=@strSQL+' AND '+str(@beginIndex+@PageSize)
    --set @strSQL=@strSQL+') AS sp WHERE ROWID BETWEEN '+str((@PageIndex-1)*@PageSize+1)
    --set @strSQL=@strSQL+' AND '+str(@PageIndex*@PageSize)
    exec (@strSQL)

    再来一个
    复制代码 代码如下:

    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    go
    -- =============================================
    -- Author: Author,,Name>
    -- Create date: Create Date,,>
    -- Description: 分页获取商品信息
    --调用方法: Proc_GetProductPaged '2','*','','xname like ''%222name%''','ORDER BY ID ASC',20,2,0,0
    -- =============================================
    ALTER PROCEDURE [dbo].[Proc_GetProductPaged]
    -- Add the parameters for the stored procedure here
    @ProductType smallint=1,--商品类型,1全部2种子3农药4肥料
    @StrSelect varchar(max)='',--显示字段
    @StrFrom varchar(max)='',--查询来源
    @StrWhere varchar(max)='',--查询条件
    @StrOrder varchar(max)='',--排序规范
    @PageSize int=50,--记录数
    @BeginIndex int=1, --开始记录位置
    -- @PageIndex int=1,--页码
    @Count int output,--记录总数
    @DoCount bit=0-- 1则统计,为0则不统计(统计会影响效率)
    AS
    BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    declare @seedtype int
    set @seedtype=2
    declare @pestype int
    set @pestype=3
    declare @ferttype int
    set @ferttype=4
    create table #product
    (
    productid uniqueidentifier,
    productname varchar(50),
    className varchar(50),
    productType int,
    createdate datetime,
    modifydate datetime
    -- companyid uniqueidentifier
    )
    declare @strSQL varchar(max)
    declare @strCount nvarchar(max)
    --计算总记录条数
    if(@DoCount!=0)
    begin
    if(@StrWhere !='')
    begin
    if(@ProductType=1)
    begin
    set @strCount='set @num=(select count(1) from Seed where '+@StrWhere+' )'+
    '+(select count(1) from pesticide where '+@StrWhere+' )'+
    '+(select count(1) from fertilizer where '+@StrWhere+' )'
    end
    else if(@ProductType=2)
    begin
    set @strCount='set @num=(select count(1) from Seed where '+@StrWhere+' )'
    end
    else if(@ProductType=3)
    begin
    set @strCount='set @num=(select count(1) from pesticide where '+@StrWhere+' )'
    end
    else if(@ProductType=4)
    begin
    set @strCount='set @num=(select count(1) from fertilizer where '+@StrWhere+' )'
    end
    end
    else
    begin
    if(@ProductType=1)
    begin
    set @strCount='set @num=(select count(1) from Seed )'+
    '+(select count(1) from pesticide )'+
    '+(select count(1) from fertilizer )'
    end
    else if(@ProductType=2)
    begin
    set @strCount='set @num=(select count(1) from Seed )'
    end
    else if(@ProductType=3)
    begin
    set @strCount='set @num=(select count(1) from pesticide )'
    end
    else if(@ProductType=4)
    begin
    set @strCount='set @num=(select count(1) from fertilizer )'
    end
    end
    EXECUTE sp_executesql @strCount ,N'@num INT output',@Count output
    end
    --分页获取数据
    if (@StrWhere !='' )
    begin
    set @StrWhere=' where '+@StrWhere
    end
    if(@ProductType=1)
    begin
    set @strSQL='insert into #product
    select s.seedid,s.seedname,cc.cropclassname,'+cast(@seedtype as varchar(1))+',s.createdate,s.modifydate
    from seed as s inner join cropclass as cc on s.cropclasscode=cc.cropclasscode'
    +@StrWhere+
    'union
    select p.pesticideid,p.pesname,pc.pesclassname,'+cast(@pestype as varchar(1))+',p.createdate,p.modifydate
    from pesticide as p inner join pesclass as pc on p.pesclasscode=pc.pesclasscode'
    +@StrWhere+
    'union
    select f.fertilizerid,f.fertname,fc.fertclassname,'+cast(@ferttype as varchar(1))+',f.createdate,f.modifydate
    from fertilizer as f inner join fertilizerclass as fc on f.fertclasscode=fc.fertclasscode'
    +@StrWhere
    set @strSQL= @strSQL+' SELECT * FROM (SELECT ROW_NUMBER() OVER ('+@StrOrder+') AS ROWID,'
    set @strSQL=@strSQL+'* FROM [#product] '--+@StrWhere
    set @strSQL=@strSQL+') AS sp WHERE ROWID BETWEEN '+str(@BeginIndex)
    set @strSQL=@strSQL+' AND '+str(@BeginIndex+@PageSize-1)
    -- set @strSQL=@strSQL+') AS sp WHERE ROWID BETWEEN '+str((@PageIndex-1)*@PageSize+1)
    -- set @strSQL=@strSQL+' AND '+str(@PageIndex*@PageSize)
    end
    else
    begin
    set @strSQL='insert into #product select '+@StrSelect+
    ' from '+@StrFrom+@StrWhere
    exec (@strSQL)
    set @strSQL=''
    set @strSQL='SELECT * FROM (SELECT ROW_NUMBER() OVER ('+@strOrder+') AS ROWID,'
    set @strSQL=@strSQL+'* FROM [#product]'+@strWhere
    set @strSQL=@strSQL+') AS sp WHERE ROWID BETWEEN '+str(@BeginIndex)
    set @strSQL=@strSQL+' AND '+str(@BeginIndex+@PageSize-1)
    -- set @strSQL=@strSQL+') AS sp WHERE ROWID BETWEEN '+str((@PageIndex-1)*@PageSize+1)
    -- set @strSQL=@strSQL+' AND '+str(@PageIndex*@PageSize)
    end
    -- else if(@ProductType=2)
    -- begin
    -- set @strSQL='insert into #product select '+@StrSelect+','+@seedtype+
    -- 'from '+@StrFrom+@StrWhere
    -- exec @strSQL
    -- set @strSQL=''
    -- set @strSQL='SELECT * FROM (SELECT ROW_NUMBER() OVER ('+@strOrder+') AS ROWID,'
    -- set @strSQL=@strSQL+@StrSelect+' FROM[#product]'+@strWhere
    -- set @strSQL=@strSQL+') AS sp WHERE ROWID BETWEEN '+str((@PageIndex-1)*@PageSize+1)
    -- set @strSQL=@strSQL+' AND '+str(@PageIndex*@PageSize)
    -- end
    -- else if(@ProductType=3)
    -- begin
    -- set @strSQL='SELECT * FROM (SELECT ROW_NUMBER() OVER ('+@strOrder+') AS ROWID,'
    -- set @strSQL=@strSQL+@StrSelect+' FROM[#product]'+@StrWhere
    -- set @strSQL=@strSQL+') AS sp WHERE ROWID BETWEEN '+str((@PageIndex-1)*@PageSize+1)
    -- set @strSQL=@strSQL+' AND '+str(@PageIndex*@PageSize)
    -- end
    -- else if(@ProductType=4)
    -- begin
    -- set @strSQL='SELECT * FROM (SELECT ROW_NUMBER() OVER ('+@strOrder+') AS ROWID,'
    -- set @strSQL=@strSQL+@StrSelect+' FROM[#product]'+@StrWhere
    -- set @strSQL=@strSQL+') AS sp WHERE ROWID BETWEEN '+str((@PageIndex-1)*@PageSize+1)
    -- set @strSQL=@strSQL+' AND '+str(@PageIndex*@PageSize)
    -- end
    exec (@strSQL)
    drop table #product
    END


    您可能感兴趣的文章:
    • sql server中千万数量级分页存储过程代码
    • sqlserver 通用分页存储过程
    • 分页存储过程(三)在sqlserver中打造更加准确的分页结果
    • SQLSERVER Pager store procedure分页存储过程
    • sqlserver 千万数量级分页存储过程代码
    • SQL Server的通用分页存储过程 未使用游标,速度更快!
    • SQL Server 2005通用分页存储过程及多表联接应用
    • mssql 高效的分页存储过程分享
    • 基于Sql Server通用分页存储过程的解决方法
    • 深入sql server 2005 万能分页存储过程的详解
    • SqlServer 2000、2005分页存储过程整理
    • 五种SQL Server分页存储过程的方法及性能比较
    上一篇:sqlserver 通用存储过程分页代码(附使用ROW_NUMBER()和不使用ROW_NUMBER()两种情况性能分析)
    下一篇:SQL2005的维护计划无法删除的解决方法
  • 相关文章
  • 

    © 2016-2020 巨人网络通讯 版权所有

    《增值电信业务经营许可证》 苏ICP备15040257号-8

    分页存储过程(一)使用sql2005的新函数构造分页存储过程 分页,存储,过程,一,使用,