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

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

<%@ 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);
}
}
}

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