sqlserver 存储过程分页，并支持条件排序，需要的朋友可以参考下。
cs页面调用代码：
代码如下:
　public int totalpage = 0;
public int pagecurrent = 1;
public int pagesize = 25;
public int rowscount = 0;
string userid, username;
public datatable dt = new datatable();
public string path, userwelcome;
public string opt,cid;
protected void page_load(object sender, eventargs e)
{
if (!ispostback)
{
if (request.params[page] == null || request.params[page].tostring().equals())
pagecurrent = 1;
else
pagecurrent=int.parse(request.params[page].tostring());
this.getpage(out totalpage, out rowscount, pagesize, pagecurrent);
}
}
//调用存储过程的函数
private void getpage(out int totalpage, out int rowscount, int pagesize, int currentpage)
{
sqlparameter[] parameters = {
new sqlparameter(@totalpage, sqldbtype.int,4),
new sqlparameter(@rowscount, sqldbtype.int,4),
new sqlparameter(@pagesize, sqldbtype.int,4),
new sqlparameter(@currentpage, sqldbtype.int,4),
new sqlparameter(@selectfields, sqldbtype.nvarchar,700),
new sqlparameter(@idfield,sqldbtype.nvarchar,50),
new sqlparameter(@orderfield, sqldbtype.nvarchar,200),
new sqlparameter(@ordertype, sqldbtype.nvarchar,2),
new sqlparameter(@tablename, sqldbtype.nvarchar,300),
new sqlparameter(@strwhere, sqldbtype.nvarchar,300),
};
parameters[0].direction = parameterdirection.output;
parameters[1].direction = parameterdirection.output;
parameters[2].value = pagesize;
parameters[3].value = currentpage;
parameters[4].value = a.rlid,a.companyname,a.website,a.isrl,a.ordernum,a.isrl,a.userid;
parameters[5].value = a.rlid;
parameters[6].value = a.isrl asc , a.ordernum ;
parameters[7].value = 1;
parameters[8].value = qiyerenling a;
parameters[9].value = 1=1;//
dataset ds = wm23abc.dbutility.dbhelpersql.runprocedure(getrecordbypage, parameters, dt);
dt = ds.tables[0];
totalpage = int.parse(parameters[0].value.tostring());
rowscount = int.parse(parameters[1].value.tostring());
}
.aspx页面代码：
公司名称 公司网址 认领状态
{
%>
排序值：
是否认领：
认领该企业 : 该企业已被认领%>
}
%>
第 页 共 条 共 页
{
%>
首　页
上一页
}
if (pagecurrent != totalpage)
{
%>
下一页
末　页
}
%>
存储过程代码：
代码如下:
create proc [dbo].[getrecordbypage]
@totalpage int output,--总页数
@rowscount int output,--总条数
@pagesize int,--每页多少数据
@currentpage int,--当前页数
@selectfields nvarchar(1000),--select 语句但是不包含select
@idfield nvarchar(50),--主键列
@orderfield nvarchar(50),--排序字段,如果是多个字段，除最后一个字段外，后面都要加排序条件（asc/desc），不包含order by,最后一个排序字段不用加排序条件
@ordertype nvarchar(4),--1升序，0降序
@tablename nvarchar(200),--表名
@strwhere nvarchar(300)--条件
as
begin
declare @recordcount float
declare @pagenum int --分页依据数
declare @compare nvarchar(50)--比较字段区分min或者max
declare @compare1 nvarchar(2) --大于号“>” 或者小于号declare @ordersql nvarchar(10)--排序字段
declare @sql nvarchar(4000)
declare @temsql nvarchar(1000)
declare @nrd int
declare @afterrows int
declare @temptablename nvarchar(10)
if(@ordertype='1')
begin
set @ordersql=' asc'
end
else
begin
set @ordersql= ' desc'
end
if(isnull(@strwhere, '')'')
set @strwhere = @strwhere
if(@strwhere='')
set @strwhere=' 1=1 '
set @temsql='select @recordcount=count(1) from '+@tablename +' where '+@strwhere
exec sp_executesql @temsql,n'@recordcount float output',@recordcount output
set @rowscount=@recordcount
set @totalpage= ceiling(@recordcount/@pagesize)
if(@currentpage>@totalpage)
set @currentpage=@totalpage
if(@currentpageset @currentpage=1
if(@pagesizeset @pagesize=1
print(@recordcount)
if(@currentpage=1)
begin
set rowcount @pagesize
set @sql='select '+ @selectfields +' from '+ @tablename +' where ' +@strwhere+' order by '+@orderfield +'
'+@ordersql +','+@idfield +' asc'
--print(@sql)
exec sp_executesql @sql
end
else if(@currentpage=@totalpage)
begin
set @afterrows=@rowscount-(@currentpage-1)*@pagesize
set rowcount @afterrows
if(@ordertype='1')
begin
set @orderfield=replace(@orderfield,'asc','lai512343975')//这里用变量将asc和desc互换，哈哈，太神了
set @orderfield=replace(@orderfield,'desc','asc')
set @orderfield=replace(@orderfield,'lai512343975','desc')
set @sql='select ' + @selectfields +' from '+ @tablename +' where ' +@strwhere+' order by '+@orderfield +' desc'+','+@idfield +' asc'
end
else
begin
set @orderfield=replace(@orderfield,'desc','lai512343975')
set @orderfield=replace(@orderfield,'asc','desc')
set @orderfield=replace(@orderfield,'lai512343975','asc')
set @sql='select ' + @selectfields +' from '+ @tablename +' where ' +@strwhere+' order by '+@orderfield +' asc ' +','+@idfield+ ' asc'
print(@sql)
end
--print(@sql)
exec sp_executesql @sql
end
else
begin
set @nrd=@pagesize* (@currentpage-1)
print(@nrd)
set rowcount @pagesize
set @sql='select ' + @selectfields +' from '+ @tablename +' where ' +@strwhere+' and '+@idfield + ' not in (select top '+ cast(@nrd as nvarchar(10))+' '+@idfield+' from '+@tablename+' where '+ @strwhere+' order by '+@orderfield +' '+@ordersql+','+@idfield +' asc) ' + ' order by '+ @orderfield + ' ' +@ordersql+','+@idfield +' asc'
exec sp_executesql @sql
--print(@sql)
end
end
go