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页面代码：

<table id="sxfstable" style="width:100%;" class="table">
<tr><td><b>公司名称</b></td><td><b>公司网址</b></td><td><b>认领状态</b></td></tr>
<%for (int i = 0; i < dt.rows.count; i++)
{
%>
<tr>
<td><%= dt.rows[i]["companyname"].tostring() %>排序值：<%= dt.rows[i]["ordernum"].tostring() %></td>
<td><%= dt.rows[i]["website"].tostring() %>
是否认领：<%=dt.rows [i]["userid"].tostring () %></td>
<td><%= dt.rows[i]["isrl"].tostring().equals("0") ? "<a href=\"javascript:;\" onclick=\"renling(event,'"+dt.rows[i]["rlid"].tostring()+"');\">认领该企业</a>" : "<font color=\"red\">该企业已被认领</font>"%></td>
</tr>
<%
}
%>
</table>
</div>
<div style="margin-left:auto; margin-right:auto; width:70%; text-align:left; font-size:9pt;">
第 <%=pagecurrent %> 页 共 <%=rowscount %> 条 共 <%=totalpage%> 页
<% if (pagecurrent != 1)
{
%>
<a href="test.aspx">首　页</a>
<a href="test.aspx?page=<%=pagecurrent-1 %>">上一页</a>

<%
}
if (pagecurrent != totalpage)
{
%>
<a href="test.aspx?page=<%=pagecurrent+1 %>">下一页</a>
<a href="test.aspx?page=<%=totalpage%>">末　页</a>
<%
}
%>
</div>

存储过程代码：

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(@currentpage<1)
set @currentpage=1
if(@pagesize<1)
set @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