sqlserver 存儲過程分頁(按多條件排序)

更新時間：2010年06月13日 00:48:17 作者：

sqlserver 存儲過程分頁，并支持條件排序，需要的朋友可以參考下。

cs頁面調(diào)用代碼：

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


//調(diào)用存儲過程的函數(shù) 

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>公司網(wǎng)址</b></td><td><b>認(rèn)領(lǐng)狀態(tài)</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() %> 
是否認(rèn)領(lǐng)：<%=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()+"');\">認(rèn)領(lǐng)該企業(yè)</a>" : "<font color=\"red\">該企業(yè)已被認(rèn)領(lǐng)</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> 

存儲過程代碼：

復(fù)制代碼代碼如下:

 
CREATE proc [dbo].[getRecordByPage] 
@TotalPage int output,--總頁數(shù) 
@RowsCount int output,--總條數(shù) 
@PageSize int,--每頁多少數(shù)據(jù) 
@CurrentPage int,--當(dāng)前頁數(shù) 
@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 --分頁依據(jù)數(shù) 
Declare @Compare nvarchar(50)--比較字段區(qū)分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 