SQL Server 存储过程分页,按多条件排序

概述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 s… @H_403_1@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",
new sqlParameter("@PageSize",
new sqlParameter("@CurrentPage",
new sqlParameter("@SelectFIElds",sqlDbType.NVarChar,700),
new sqlParameter("@IDFIEld",50),
new sqlParameter("@OrderFIEld",200),
new sqlParameter("@OrderType",2),
new sqlParameter("@tablename",300),
new sqlParameter("@strWhere",
};
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,'lai512343975')
set @OrderFIEld=REPLACE(@OrderFIEld,'desc')
set @OrderFIEld=REPLACE(@OrderFIEld,'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

出处：http://www.cnblogs.com/jxcia_Lai/

总结

以上是内存溢出为你收集整理的SQL Server 存储过程分页,按多条件排序全部内容，希望文章能够帮你解决SQL Server 存储过程分页,按多条件排序所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。