SQL Server 存储过程分页代码
2022-11-12 09:52:58
内容摘要
这篇文章主要为大家详细介绍了SQL Server 存储过程分页代码,具有一定的参考价值,可以用来参考一下。
对此感兴趣的朋友,看看idc笔记做的技术笔记!
代码如下:
declare @TotalC
文章正文
这篇文章主要为大家详细介绍了SQL Server 存储过程分页代码,具有一定的参考价值,可以用来参考一下。
对此感兴趣的朋友,看看idc笔记做的技术笔记!
代码如下:
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 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 | <code> declare @TotalCount int declare @TotalPageCount int exec P_viewPage_A 'type1' , '*' , 'id' , '' , 'id asc' ,1,0,4,3,@TotalCount output,@TotalPageCount output select * from type1 Create PROC P_viewPage_A /* nzperfect [no_mIss] 高效通用分页存储过程(双向检索) 2007.5.7 QQ:34813284 敬告:适用于单一主键或存在唯一值列的表或视图 ps:Sql语句为8000字节,调用时请注意传入参数及sql总长度不要超过指定范围 */ @TableName VARCHAR(200), --表名 @FieldList VARCHAR(2000), --显示列名,如果是全部字段则为* @PrimaryKey VARCHAR(100), --单一主键或唯一值键 @Where VARCHAR(2000), --查询条件 不含 'where' 字符,如id>10 and len(userid)>9 @Order VARCHAR(1000), --排序 不含 'order by' 字符,如id asc,userid desc,必须指定asc或desc --注意当@SortType=3时生效,记住一定要在最后加上主键,否则会让你比较郁闷 @SortType INT, --排序规则 1:正序asc 2:倒序desc 3:多列排序方法 @RecorderCount INT, --记录总数 0:会返回总记录 @PageSize INT, --每页输出的记录数 @PageIndex INT, --当前页数 @TotalCount INT OUTPUT, --记返回总记录 @TotalPageCount INT OUTPUT --返回总页数 AS SET NOCOUNT ON IF ISNULL(@TotalCount, '' ) = '' SET @TotalCount = 0 SET @Order = RTRIM(LTRIM(@Order)) SET @PrimaryKey = RTRIM(LTRIM(@PrimaryKey)) SET @FieldList = REPLACE(RTRIM(LTRIM(@FieldList)), ' ' , '' ) WHILE CHARINDEX( ', ' ,@Order) > 0 OR CHARINDEX( ' ,' ,@Order) > 0 BEGIN SET @Order = REPLACE(@Order, ', ' , ',' ) SET @Order = REPLACE(@Order, ' ,' , ',' ) END IF ISNULL(@TableName, '' ) = '' OR ISNULL(@FieldList, '' ) = '' OR ISNULL(@PrimaryKey, '' ) = '' OR @SortType < 1 OR @SortType >3 OR @RecorderCount < 0 OR @PageSize < 0 OR @PageIndex < 0 BEGIN PRINT ( 'ERR_00' ) RETURN END IF @SortType = 3 BEGIN IF (UPPER(RIGHT(@Order,4))!= ' ASC' AND UPPER(RIGHT(@Order,5))!= ' DESC' ) BEGIN PRINT ( 'ERR_02' ) RETURN END END DECLARE @new_where1 VARCHAR(1000) DECLARE @new_where2 VARCHAR(1000) DECLARE @new_order1 VARCHAR(1000) DECLARE @new_order2 VARCHAR(1000) DECLARE @new_order3 VARCHAR(1000) DECLARE @Sql VARCHAR(8000) DECLARE @SqlCount NVARCHAR(4000) IF ISNULL(@where, '' ) = '' BEGIN SET @new_where1 = ' ' SET @new_where2 = ' WHERE ' END ELSE BEGIN SET @new_where1 = ' WHERE ' + @where SET @new_where2 = ' WHERE ' + @where + ' AND ' END IF ISNULL(@order, '' ) = '' OR @SortType = 1 OR @SortType = 2 BEGIN IF @SortType = 1 BEGIN SET @new_order1 = ' ORDER BY ' + @PrimaryKey + ' ASC' SET @new_order2 = ' ORDER BY ' + @PrimaryKey + ' DESC' END IF @SortType = 2 BEGIN SET @new_order1 = ' ORDER BY ' + @PrimaryKey + ' DESC' SET @new_order2 = ' ORDER BY ' + @PrimaryKey + ' ASC' END END ELSE BEGIN SET @new_order1 = ' ORDER BY ' + @Order END IF @SortType = 3 AND CHARINDEX( ',' +@PrimaryKey+ ' ' , ',' +@Order)>0 BEGIN SET @new_order1 = ' ORDER BY ' + @Order SET @new_order2 = @Order + ',' SET @new_order2 = REPLACE(REPLACE(@new_order2, 'ASC,' , '{ASC},' ), 'DESC,' , '{DESC},' ) SET @new_order2 = REPLACE(REPLACE(@new_order2, '{ASC},' , 'DESC,' ), '{DESC},' , 'ASC,' ) SET @new_order2 = ' ORDER BY ' + SUBSTRING(@new_order2,1,LEN(@new_order2)-1) IF @FieldList <> '*' BEGIN SET @new_order3 = REPLACE(REPLACE(@Order + ',' , 'ASC,' , ',' ), 'DESC,' , ',' ) SET @FieldList = ',' + @FieldList WHILE CHARINDEX( ',' ,@new_order3)>0 BEGIN IF CHARINDEX(SUBSTRING( ',' +@new_order3,1,CHARINDEX( ',' ,@new_order3)), ',' +@FieldList+ ',' )>0 BEGIN SET @FieldList = @FieldList + ',' + SUBSTRING(@new_order3,1,CHARINDEX( ',' ,@new_order3)) END SET @new_order3 = SUBSTRING(@new_order3,CHARINDEX( ',' ,@new_order3)+1,LEN(@new_order3)) END SET @FieldList = SUBSTRING(@FieldList,2,LEN(@FieldList)) END END SET @SqlCount = 'SELECT @TotalCount=COUNT(*),@TotalPageCount=CEILING((COUNT(*)+0.0)/' + CAST(@PageSize AS VARCHAR)+ ') FROM ' + @TableName + @new_where1 IF @RecorderCount = 0 BEGIN EXEC SP_EXECUTESQL @SqlCount,N '@TotalCount INT OUTPUT,@TotalPageCount INT OUTPUT' , @TotalCount OUTPUT,@TotalPageCount OUTPUT END ELSE BEGIN SELECT @TotalCount = @RecorderCount END IF @PageIndex > CEILING((@TotalCount+0.0)/@PageSize) BEGIN SET @PageIndex = CEILING((@TotalCount+0.0)/@PageSize) END IF @PageIndex = 1 OR @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize) BEGIN IF @PageIndex = 1 --返回第一页数据 BEGIN SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ' + @TableName + @new_where1 + @new_order1 END IF @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize) --返回最后一页数据 BEGIN SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM (' + 'SELECT TOP ' + STR( ABS (@PageSize*@PageIndex-@TotalCount-@PageSize)) + ' ' + @FieldList + ' FROM ' + @TableName + @new_where1 + @new_order2 + ' ) AS TMP ' + @new_order1 END END ELSE BEGIN IF @SortType = 1 --仅主键正序排序 BEGIN IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索 BEGIN SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ' + @TableName + @new_where2 + @PrimaryKey + ' > ' + '(SELECT MAX(' + @PrimaryKey + ') FROM (SELECT TOP ' + STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey + ' FROM ' + @TableName + @new_where1 + @new_order1 + ' ) AS TMP) ' + @new_order1 END ELSE --反向检索 BEGIN SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM (' + 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ' + @TableName + @new_where2 + @PrimaryKey + ' < ' + '(SELECT MIN(' + @PrimaryKey + ') FROM (SELECT TOP ' + STR(@TotalCount-@PageSize*@PageIndex) + ' ' + @PrimaryKey + ' FROM ' + @TableName + @new_where1 + @new_order2 + ' ) AS TMP) ' + @new_order2 + ' ) AS TMP ' + @new_order1 END END IF @SortType = 2 --仅主键反序排序 BEGIN IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索 BEGIN SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ' + @TableName + @new_where2 + @PrimaryKey + ' < ' + '(SELECT MIN(' + @PrimaryKey + ') FROM (SELECT TOP ' + STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey + ' FROM ' + @TableName + @new_where1 + @new_order1 + ') AS TMP) ' + @new_order1 END ELSE --反向检索 BEGIN SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM (' + 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ' + @TableName + @new_where2 + @PrimaryKey + ' > ' + '(SELECT MAX(' + @PrimaryKey + ') FROM (SELECT TOP ' + STR(@TotalCount-@PageSize*@PageIndex) + ' ' + @PrimaryKey + ' FROM ' + @TableName + @new_where1 + @new_order2 + ' ) AS TMP) ' + @new_order2 + ' ) AS TMP ' + @new_order1 END END IF @SortType = 3 --多列排序,必须包含主键,且放置最后,否则不处理 BEGIN IF CHARINDEX( ',' + @PrimaryKey + ' ' , ',' + @Order) = 0 BEGIN PRINT ( 'ERR_02' ) RETURN END IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索 BEGIN SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( ' + 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( ' + ' SELECT TOP ' + STR(@PageSize*@PageIndex) + ' ' + @FieldList + ' FROM ' + @TableName + @new_where1 + @new_order1 + ' ) AS TMP ' + @new_order2 + ' ) AS TMP ' + @new_order1 END ELSE --反向检索 BEGIN SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( ' + 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( ' + ' SELECT TOP ' + STR(@TotalCount-@PageSize *@PageIndex+@PageSize) + ' ' + @FieldList + ' FROM ' + @TableName + @new_where1 + @new_order2 + ' ) AS TMP ' + @new_order1 + ' ) AS TMP ' + @new_order1 END END END EXEC (@Sql) GO </code> |
12下一页阅读全文
对此感兴趣的朋友,看看idc笔记做的技术笔记!
代码如下:
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 | <strong> <code> private void fyDB() { DataTable dt = new DataTable(); string con = System.Configuration.ConfigurationSettings.AppSettings[ "connectionstring" ]; SqlConnection conn = new SqlConnection(con); SqlCommand comm = new SqlCommand( "P_viewPage_A" , conn); //建立SqlCommand对象 comm.CommandType = CommandType.StoredProcedure; //设置SqlCommand对象执行类型为存储过程 comm.Parameters.Add( "@TableName" , SqlDbType.VarChar ,200); //向Parameters参数列表添加参数 comm.Parameters.Add( "@FieldList" , SqlDbType.VarChar, 2000); comm.Parameters.Add( "@PrimaryKey" , SqlDbType.VarChar, 100); comm.Parameters.Add( "@where" , SqlDbType.VarChar, 2000); comm.Parameters.Add( "@Order" , SqlDbType.VarChar, 1000); comm.Parameters.Add( "@SortType" , SqlDbType.Int); comm.Parameters.Add( "@RecorderCount" , SqlDbType.Int); comm.Parameters.Add( "@PageSize" , SqlDbType.Int); comm.Parameters.Add( "@PageIndex" , SqlDbType.Int); comm.Parameters.Add( "@TotalCount" , SqlDbType.Int); comm.Parameters.Add( "@TotalPageCount" , SqlDbType.Int); comm.Parameters[ "@TotalCount" ].Direction = ParameterDirection.Output; //设置参数的输出类型 comm.Parameters[ "@TotalPageCount" ].Direction = ParameterDirection.Output; //设置参数的输出类型 comm.Parameters[ "@TableName" ].Value = "type1" ; //表名 comm.Parameters[ "@FieldList" ].Value = "*" ; //显示列名,如果是全部字段则为* comm.Parameters[ "@PrimaryKey" ].Value = "id" ; //单一主键或唯一值键 comm.Parameters[ "@where" ].Value = "" ; //查询条件 不含'where'字符,如id>10 and len(userid)>9 comm.Parameters[ "@Order" ].Value = "id asc" ; //排序 不含'order by'字符,如id asc,userid desc,必须指定asc或desc comm.Parameters[ "@SortType" ].Value = 1; //排序规则 1:正序asc 2:倒序desc 3:多列排序方法 comm.Parameters[ "@RecorderCount" ].Value = 0; //记录总数 0:会返回总记录 comm.Parameters[ "@PageSize" ].Value = 2; //每页输出的记录数 int id1; if (Request.QueryString[ "id" ] == null) id1 = 1; else id1 = Convert.ToInt32(Request.QueryString[ "id" ]); comm.Parameters[ "@PageIndex" ].Value = id1; //当前页数 conn.Open(); // SqlDataReader asr = comm.ExecuteReader(); //int dtr = (int)comm.ExecuteScalar(); SqlDataReader sdr = comm.ExecuteReader(); int i=0; //while (sdr.Read()) //{ // dt.Rows[i][0] = sdr.GetValue(0); // dt.Rows[i][1] = sdr.GetValue(1); //} dt = ConvertDataReaderToDataTable(sdr); GridView1.DataSource = dt; GridView1.DataBind(); // dt.Rows[0][0]; sdr.Close(); // Response.Write(dtr); Response.Write(comm.Parameters[ "@TotalCount" ].Value + "<br>" ); Response.Write(comm.Parameters[ "@TotalPageCount" ].Value + "<br>" ); // if((Int32)comm.Parameters["RETURN_VALUE"].Value==0) conn.Close(); //string conStr = System.Configuration.ConfigurationSettings.AppSettings["connectionstring"]; //SqlConnection connection = new SqlConnection(conStr); //DataSet dataSet = new DataSet(); //connection.Open(); //SqlDataAdapter sqlDA = new SqlDataAdapter(); //sqlDA.SelectCommand = BuildQueryCommand(connection, "P_viewPage_A", parameters); //sqlDA.Fill(dataSet, tableName); //connection.Close(); //return dataSet; } #region 将DataReader 转为 DataTable /// <summary> /// 将DataReader 转为 DataTable /// </summary> /// <param name="DataReader">DataReader</param> public static DataTable ConvertDataReaderToDataTable(SqlDataReader reader) { try { DataTable objDataTable = new DataTable(); int intFieldCount = reader.FieldCount; for (int intCounter = 0; intCounter < intFieldCount; ++intCounter) { objDataTable.Columns.Add(reader.GetName(intCounter), reader.GetFieldType(intCounter)); } objDataTable.BeginLoadData(); object[] objValues = new object[intFieldCount]; while (reader.Read()) { reader.GetValues(objValues); objDataTable.LoadDataRow(objValues, true); } reader.Close(); objDataTable.EndLoadData(); return objDataTable; } catch (Exception ex) { throw new Exception( "转换DataReader为DataTable出错!" , ex); } } #endregion </code></strong> |
上一页12阅读全文
注:关于SQL Server 存储过程分页代码的内容就先介绍到这里,更多相关文章的可以留意
代码注释