SQL SERVER存储过程分页(按多条件排序)
2022-11-12 09:53:54
内容摘要
这篇文章主要为大家详细介绍了SQL SERVER存储过程分页(按多条件排序),具有一定的参考价值,可以用来参考一下。
对此感兴趣的朋友,看看idc笔记做的技术笔记!cs页面调用代码:
代
文章正文
这篇文章主要为大家详细介绍了SQL SERVER存储过程分页(按多条件排序),具有一定的参考价值,可以用来参考一下。
对此感兴趣的朋友,看看idc笔记做的技术笔记!
cs页面调用代码:代码如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 | <code> 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> </code> |
代码如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 | <code> 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 </code> |
注:关于SQL SERVER存储过程分页(按多条件排序)的内容就先介绍到这里,更多相关文章的可以留意
代码注释