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,

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 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="http://news.558idc.com/test.aspx">首　页</a> <a href="http://news.558idc.com/test.aspx?page=<%=PageCurrent-1 %>">上一页</a> <% } if (PageCurrent != TotalPage) { %> <a href="http://news.558idc.com/test.aspx?page=<%=PageCurrent+1 %>">下一页</a> <a href="http://news.558idc.com/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