浏览主题：?b??i????,??b??i???j?p配s网??st???网招???????京京gc?ex网招?t?????
主题：?b??i????,??b??i???j?p配s网??st???网招???????京京gc?ex网招?t?????
sql 程序代码 1 CREATE PROC P_viewPage 2 /* 3 no_mIss 分页存储过程 2007.2.20 QQ:34813284 4 适用于单一主键或存在唯一值列的表或视图 5 */ 6 @TableName VARCHAR(200), --表名 7 @FieldList VARCHAR(2000), --显示列名 8 @PrimaryKey VARCHAR(100), --单一主键或唯一值键 9 @Where VARCHAR(1000), --查询条件 不含'where'字符 10 @Order VARCHAR(1000), --排序 不含'order by'字符，如id asc,userid desc，当@SortType=3时生效 11 @SortType INT, --排序规则 1:正序asc 2:倒序desc 3:多列排序 12 @RecorderCount INT, --记录总数 0:会返回总记录 13 @PageSize INT, --每页输出的记录数 14 @PageIndex INT, --当前页数 15 @TotalCount INT OUTPUT, --返回记录总数 16 @TotalPageCount INT OUTPUT --返回总页数 17 AS 18 SET NOCOUNT ON 19 IF ISNULL(@TableName,'') = '' OR ISNULL(@FieldList,'') = '' 20 OR ISNULL(@PrimaryKey,'') = '' 21 OR @SortType < 1 OR @SortType >3 22 OR @RecorderCount < 0 OR @PageSize < 0 OR @PageIndex < 0 23 BEGIN 24 RETURN 25 END 26 27 DECLARE @new_where1 VARCHAR(1000) 28 DECLARE @new_where2 VARCHAR(1000) 29 DECLARE @new_order VARCHAR(1000) 30 DECLARE @Sql VARCHAR(8000) 31 DECLARE @SqlCount NVARCHAR(4000) 32 IF ISNULL(@where,'') = '' 33 BEGIN 34 SET @new_where1 = ' ' 35 SET @new_where2 = ' WHERE ' 36 END 37 ELSE 38 BEGIN 39 SET @new_where1 = ' WHERE ' + @where 40 SET @new_where2 = ' WHERE ' + @where + ' AND ' 41 END 42 IF ISNULL(@order,'') = '' OR @SortType = 1 OR @SortType = 2 43 BEGIN 44 IF @SortType = 1 SET @new_order = ' ORDER BY ' + @PrimaryKey + ' ASC' 45 IF @SortType = 2 SET @new_order = ' ORDER BY ' + @PrimaryKey + ' DESC' 46 END 47 ELSE 48 BEGIN 49 SET @new_order = ' ORDER BY ' + @Order 50 END 51 SET @SqlCount = 'SELECT @TotalCount=COUNT(*),@TotalPageCount=CEILING((COUNT(*)+0.0)/' 52 + CAST(@PageSize AS VARCHAR)+') FROM ' + @TableName + @new_where1 53 54 IF @RecorderCount = 0 55 BEGIN 56 EXEC SP_EXECUTESQL @SqlCount,N'@TotalCount INT OUTPUT,@TotalPageCount INT OUTPUT', 57 @TotalCount OUTPUT,@TotalPageCount OUTPUT 58 END 59 ELSE 60 BEGIN 61 SELECT @TotalCount = @RecorderCount 62 END 63 IF @PageIndex > CEILING((@TotalCount+0.0)/@PageSize) 64 BEGIN 65 SET @PageIndex = CEILING((@TotalCount+0.0)/@PageSize) 66 END 67 IF @PageIndex = 1 68 BEGIN 69 SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ' 70 + @TableName + @new_where1 + @new_order 71 END 72 ELSE 73 BEGIN 74 IF @SortType = 1 75 BEGIN 76 SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ' 77 + @TableName + @new_where2 + @PrimaryKey + ' > ' 78 + '(SELECT MAX(' + @PrimaryKey + ') FROM (SELECT TOP ' 79 + STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey 80 + ' FROM ' + @TableName 81 + @new_where1 + @new_order +' ) AS TMP) '+ @new_order 82 END 83 IF @SortType = 2 84 BEGIN 85 SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ' 86 + @TableName + @new_where2 + @PrimaryKey + ' < ' 87 + '(SELECT MIN(' + @PrimaryKey + ') FROM (SELECT TOP ' 88 + STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey 89 +' FROM '+ @TableName 90 + @new_where1 + @new_order + ') AS TMP) '+ @new_order 91 END 92 IF @SortType = 3 93 BEGIN 94 IF CHARINDEX(',',@Order) = 0 BEGIN RETURN END 95 SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ' 96 + @TableName + @new_where2 + @PrimaryKey + ' NOT IN (SELECT TOP ' 97 + STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey 98 + ' FROM ' + @TableName + @new_where1 + @new_order + ')' 99 + @new_order 100 END 101 END 102 EXEC(@Sql) 103 GO 104 105 CREATE PROC P_public_ViewPage 106 /* 107 no_mIss 通用分页存储过程 2007.3.1 QQ:34813284 108 适用于联合主键/单主键/存在能确定唯一行列/存在能确定唯一行的多列 (用英文,隔开) 109 调用： 110 第一页查询时返回总记录和总页数及第一页记录： 111 EXECUTE P_public_ViewPage_per 'TableName','col1,col2,col3,col4','pk1,pk2,pk3', 112 'col5>0 and col7<9','pk1 asc,pk2 asc,pk3 asc',0,10,1, 113 @TotalCount OUTPUT,@TotalPageCount OUTPUT 114 其它页调用，比如第89页(假设第一页查询时返回总记录为2000000)： 115 EXECUTE P_public_ViewPage_per 'TableName','col1,col2,col3,col4','pk1,pk2,pk3', 116 'col5>0 and col7<9','pk1 asc,pk2 asc,pk3 asc',2000000,10,89, 117 @TotalCount OUTPUT,@TotalPageCount OUTPUT 118 */ 119 @TableName VARCHAR(200), --表名 120 @FieldList VARCHAR(2000), --显示列名 121 @PrimaryKey VARCHAR(100), --单一主键或唯一值键或联合主键列表(用英文,隔开)或能确定唯一行的多列列表(用英文,隔开) 122 @Where VARCHAR(1000), --查询条件 不含'where'字符 123 @Order VARCHAR(1000), --排序 不含'order by'字符,用英文,隔开 124 @RecorderCount INT, --记录总数 0:会返回总记录 125 @PageSize INT, --每页输出的记录数 126 @PageIndex INT, --当前页数 127 @TotalCount INT OUTPUT, --返回记录总数 128 @TotalPageCount INT OUTPUT --返回总页数 129 AS 130 SET NOCOUNT ON 131 132 SET @FieldList = REPLACE(@FieldList,' ','') 133 IF @FieldList = '*' 134 BEGIN SET @FieldList = 'A.*'END 135 ELSE 136 BEGIN 137 SET @FieldList = 'A.' + REPLACE(@FieldList,',',',A.') 138 END 139 140 WHILE CHARINDEX(', ',@Order)>0 141 BEGIN 142 SET @Order = REPLACE(@Order,', ',',') 143 END 144 IF ISNULL(@TableName,'') = '' OR ISNULL(@PrimaryKey,'') = '' 145 OR @RecorderCount < 0 OR @PageSize < 0 OR @PageIndex < 0 146 BEGIN 147 RETURN 148 END 149 150 DECLARE @new_where1 VARCHAR(1000) 151 DECLARE @new_where2 VARCHAR(1000) 152 DECLARE @new_where3 VARCHAR(1000) 153 DECLARE @new_where4 VARCHAR(1000) 154 DECLARE @new_order1 VARCHAR(1000) 155 DECLARE @new_order2 VARCHAR(1000) 156 DECLARE @Fields VARCHAR(1000) 157 DECLARE @Sql VARCHAR(8000) 158 DECLARE @SqlCount NVARCHAR(4000) 159 SET @Fields = @PrimaryKey + ',' 160 SET @new_where2 = '' 161 SET @new_where4 = '' 162 163 IF ISNULL(@where,'') = '' 164 BEGIN 165 SET @new_where1 = ' ' 166 SET @new_where3 = ' WHERE ' 167 END 168 ELSE 169 BEGIN 170 SET @new_where1 = ' WHERE ' + @where + ' ' 171 SET @new_where3 = ' WHERE 1=1 ' 172 + REPLACE(' AND ' + @where,' AND ',' AND A.')+ ' AND ' 173 END 174 175 WHILE CHARINDEX(',',@Fields)>0 176 BEGIN 177 SET @new_where2 = @new_where2 178 + 'A.' + LTRIM(LEFT(@Fields,CHARINDEX(',',@Fields)-1)) 179 + ' = B.' + LTRIM(LEFT(@Fields,CHARINDEX(',',@Fields)-1)) + ' AND ' 180 SET @new_where4 = @new_where4 181 + 'B.' + LTRIM(LEFT(@Fields,CHARINDEX(',',@Fields)-1)) + ' IS NULL AND ' 182 SET @Fields = SUBSTRING(@Fields,CHARINDEX(',',@Fields)+1,LEN(@Fields)) 183 END 184 SET @new_where2 = LEFT(@new_where2,LEN(@new_where2)-4) 185 SET @new_where4 = LEFT(@new_where4,LEN(@new_where4)-4) 186 IF ISNULL(@order,'') = '' 187 BEGIN 188 SET @new_order1 = '' 189 SET @new_order2 = '' 190 END 191 ELSE 192 BEGIN 193 SET @new_order1 = ' ORDER BY ' + @Order 194 SET @new_order2 = ' ORDER BY ' 195 + RIGHT(REPLACE(',' + @Order,',',', A.' ), 196 LEN(REPLACE(',' + @Order,',',', A.' ))-1) 197 END 198 SET @SqlCount = 'SELECT @TotalCount=COUNT(*),@TotalPageCount=CEILING((COUNT(*)+0.0)/' 199 + CAST(@PageSize AS VARCHAR)+') FROM ' + @TableName 200 + ' A ' + @new_where1 201 202 IF @RecorderCount = 0 203 BEGIN 204 EXEC SP_EXECUTESQL @SqlCount,N'@TotalCount INT OUTPUT,@TotalPageCount INT OUTPUT', 205 @TotalCount OUTPUT,@TotalPageCount OUTPUT 206 END 207 ELSE 208 BEGIN 209 SELECT @TotalCount = @RecorderCount 210 END 211 IF @PageIndex > CEILING((@TotalCount+0.0)/@PageSize) 212 BEGIN 213 SET @PageIndex = CEILING((@TotalCount+0.0)/@PageSize) 214 END 215 IF @PageIndex = 1 216 BEGIN 217 SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ' 218 + @TableName + ' A'+ @new_where1 + @new_order1 219 END 220 ELSE 221 BEGIN 222 SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ' 223 + @TableName + ' A LEFT JOIN (SELECT TOP ' 224 + STR(@PageSize*(@PageIndex-1)) 225 + ' ' + @PrimaryKey + ' FROM ' + @TableName + @new_where1 226 + @new_order1 + ' )B ON ' + @new_where2 + @new_where3 227 + @new_where4 + @new_order2 228 END 229 EXEC(@Sql) 230 GO 231 如果测试出有误，请指正，谢谢。 编辑标志 本帖最后由［完美行动］在 2007-3-5 0:49:49 编辑 |
|
这个高亮如果copy时把行号去掉，就再好不过了。 |
