分页存储过程（一）使用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 下载地址：
<%@ 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>人 共<asp:Label ID="lblPageCount" runat="server" Text="Label"></asp:Label>页
现为第<asp:Label ID="lblPageIndex" runat="server" Text="Label"></asp:Label>页
<asp:LinkButton ID="firstPage" runat="server" CommandArgument="first"
oncommand="LinkButton_Command">首页</asp:LinkButton>
<asp:LinkButton ID="nextPage" runat="server" CommandArgument="next"
oncommand="LinkButton_Command">下一页</asp:LinkButton>
<asp:LinkButton ID="prevPage" runat="server" CommandArgument="prev"
oncommand="LinkButton_Command">上一页</asp:LinkButton>
<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