sqlserver存储过程分页(按多条件排序)

作者：听海的音_104 | 来源：互联网 | 2022-11-18 18:16

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

<%for (int i = 0; i {
%>

<%
}
%>

公司名称	公司网址	认领状态
<%= dt.Rows[i]["companyName"].ToString() %>排序值：<%= dt.Rows[i]["ordernum"].ToString() %>	<%= dt.Rows[i]["webSite"].ToString() %> 是否认领：<%=dt.Rows [i]["userid"].ToString () %>	<%= dt.Rows[i]["isRL"].ToString().Equals("0") ? "认领该企业" : "该企业已被认领"%>

第 <%=PageCurrent %> 页共 <%=RowsCount %> 条共 <%=TotalPage%> 页
<% if (PageCurrent != 1)
{
%>
首　页
上一页

<%
}
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(@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 