分页存储过程（一）使用sql2005的新函数构造分页存储过程

MsSQL 前端之家
前端之家收集整理的这篇文章主要介绍了分页存储过程（一）使用sql2005的新函数构造分页存储过程，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

其实在很多时候设计的度还是要把握的，不至于让自己陷入【非要把它设计成万能的分页存储过程】的怪圈中才是最重要的，因为我们还要留出时间还解决其他的很多问题，个人认为适度就可以了，留出一定的空间。也因为万能是不存在的，万物在一定的范畴之内都是合理的，出了范畴可能就没有合理的了。

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

1、Not in select top

2、idselect top

3、sql

链接

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

2005CTE,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#中常用的分页存储过程小结中的下列部分

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,@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,@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,'+@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,@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-3E41584B6CE5&displaylang=en

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

代码如下:
<%@ Control Language="C#" AutoEventWireup="true" CodeBehind="StudentDetailsTable.ascx.cs" Inherits="Kimbanx.UCS.ForeignStudentAdmin.UserControl.UserControl.StudentDetailsTable" %>

级节点学员
人onselectedindexchanged="ddlPageSize_SelectedIndexChanged">
人共页
现为第页
oncommand="LinkButton_Command">首页
oncommand="LinkButton_Command">下一页
oncommand="LinkButton_Command">上一页
oncommand="LinkButton_Command">末页


EmptyDataText="没有符合条件的数据">


runat="server" />


































示例代码，后台代码
代码如下:
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;
}
///
/// 获取QueryString传递参数
///
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
/////
///// 获取学员数据
/////
///// 显示的字段
/////
/////查询条件
///// 显示条数
/////
/////
//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","tx_xyzl");
// _db.AddInParameter(_command,"StrWhere",strWhere );
// _db.AddInParameter(_command,"StrOrder","id");
// _db.AddInParameter(_command,"PageSize",DbType.Int32,pageSize );
// _db.AddInParameter(_command,"PageIndex",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;
}
///
/// 绑定分页之后的数据
///
/// 显示的数据量
///
protected void BindStudentData(int pageSize,int pageIndex)
{
switch (_queryStringWhere)
{
case "jx":
this.gvStudent.DataSource = Helper.StudentPagingResult(
"zpadress,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,
string.Format("dwbh='{0}' and gj='{1}'",
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,120) and
dw.bysj<=convert(datetime,120)) ",
"s.id",
pageIndex);
this.gvStudent.DataBind();
break;
case "new":
this.gvStudent.DataSource = Helper.StudentPagingResult(
"s.zpadress,
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("您没有相应的权限，请联系管理员"));
}
}
///
/// 获取班级信息，班级全称，班级级别
///
///
///
protected ClassEntity GetClassInfo(string classCode)
{
ClassEntity entity = new ClassEntity();
entity.Code = classCode;
_command = _db.GetStoredProcCommand("ClassInfo");
_db.AddInParameter(_command,"bh",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:
-- Create date: <2010-03-03>
-- Description: 调用方法: sp_Pager2005 'xtest','*','ORDER BY ID ASC','xname like ''%222name%''',2,20,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:
-- Create date:
-- Description: 分页获取商品信息
--调用方法: Proc_GetProductPaged '2','',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,@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

原文链接：https://www.f2er.com/mssql/66380.html
sql2005分页存储过程

猜你在找的MsSQL相关文章

SQL Server2012高可用之日志传送测试
（一）日志传送架构 （1.1）相关服务器 主服务器 ：用于生产的服务器，上面运行这生产SQL ...
SQL server事物复制报错：要复制的 LOB 数据的长度(xxxxx)超出了配置的最大值 65536
(一)事故背景 最近在SQL Server 2012生产数据库上配置完事物复制（发布订阅）后，生产数据...
SQL Server2012高可用之事物复制(发布订阅)测试
（一）测试目的 目前公司使用的SQL SERVER 2012高可用环境为主备模式，其中主库可执行读写...
使用zabbix监控sql server的发布订阅
（一）背景个人在使用sql server时，用到了sql server的发布订阅来做主从同步，类似MySQL的...
SQL学习（四）集合运算
UNION和OR谓词 找出 product 和 product2 中售价高于 500 的商品的基本信息. select * fro...
SQL学习（五） 高级处理
5.1 请说出针对本章中使用的 product（商品）表执行如下 SELECT 语句所能得到的结果。 SEL...
SQL学习(一）初识数据库
datawhale组队学习SQL task01
SQL学习(二）基础查询与排序
datawhale数据库组队学习第二天打卡
SQL Server 2005 返回修改后的数据
今天在公司看到同事写了个SQL2005的新特性的文章，觉得很实用，在这里和大家分享下。原著作...
SQL Server 数据库最小宕机迁移方案
一、目的 在做SQL Server数据库维护的时候，当上司要求我们把几十G的数据文件搬动到其它服...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问