| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_helpsort | | 31 Aug 14 | Defects Dependencies |
1 2 /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */ 3 /* 4.8 1.1 06/14/90 sproc/src/fixindex */ 4 /* 5 ** Messages for "sp_helpsort" 17690 6 ** 7 ** 17690, "Character Set = " 8 ** 17691, "Sort Order = " 9 ** 17692, "Sort Order Description" 10 ** 17693, "Characters, in Order" 11 ** 17694, "Class 2 Character Set" 12 */ 13 create procedure sp_helpsort 14 AS 15 set nocount on 16 17 declare @sortid tinyint, @charid tinyint, @status tinyint 18 declare @csclass int 19 declare @image binary(1) 20 declare @count int, @end_flag int 21 22 23 declare @string varchar(66) 24 declare @msg varchar(1024) 25 26 27 if @@trancount = 0 28 begin 29 set chained off 30 end 31 32 set transaction isolation level 1 33 34 create table #helpsort3(string varchar(66)) 35 36 /* 37 ** Get the sortorder ID, character set ID, and status 38 ** For SMP or SDC, one row is expected. 39 */ 40 select @sortid = value from master..syscurconfigs where config = 123 41 42 43 select @charid = value from master..syscurconfigs where config = 131 44 45 46 select @image = convert(binary(1), definition) 47 from master.dbo.syscharsets where id = @sortid and csid = @charid 48 49 /* 50 ** Get the name and description of the character set and sort order 51 */ 52 53 54 if (@sortid is NULL) 55 BEGIN 56 select @sortid = 0, @charid = 0, @status = 0, @csclass = 0 57 /* 17690, "Character Set = " */ 58 exec sp_getmessage 17690, @msg out 59 insert #helpsort3 values (@msg + " 0, ascii_8") 60 insert #helpsort3 values (" 8-bit ASCII Character Set - System Default") 61 /* 17691, "Sort Order = " */ 62 exec sp_getmessage 17691, @msg out 63 insert #helpsort3 values (@msg + " 0, bin_ascii_8") 64 insert #helpsort3 values (" Binary Ordering - System Default") 65 END 66 else 67 BEGIN 68 select @status = convert(tinyint, @image) 69 70 /* 17690, "Character Set = " */ 71 /* When "type" > 2000, the row represents a Unilib sort order. */ 72 /* Need to check both "id" and "type" fields when mapping a charid. */ 73 exec sp_getmessage 17690, @msg out 74 select @string = @msg + " " + convert(varchar(4), id) + ", " + name, 75 @csclass = type 76 from master.dbo.syscharsets 77 where id = @charid and type <= 2000 78 79 insert into #helpsort3 values (@string) 80 81 select @count = 1 82 select @end_flag = 1 83 84 while (@end_flag != 0) 85 begin 86 select @string = substring(description, @count, 62) 87 from master.dbo.syscharsets 88 where id = @charid and type <= 2000 89 select @end_flag = isnull(ascii(@string), 0) 90 91 if (@end_flag != 0) 92 begin 93 select @string = " " + @string 94 insert #helpsort3 values (@string) 95 end 96 select @count = @count + 62 97 end 98 99 /* Check if class 2 character set. If so, tell 'em. */ 100 if (@csclass = 1002) 101 begin 102 /* 17694, "Class 2 Character Set" */ 103 exec sp_getmessage 17694, @msg out 104 insert #helpsort3 values (" " + @msg) 105 end 106 107 /* 17691, "Sort Order = " */ 108 exec sp_getmessage 17691, @msg out 109 select @string = @msg + " " + convert(varchar(4), id) + ", " + name 110 from master.dbo.syscharsets 111 where id = @sortid and csid = @charid 112 113 insert into #helpsort3 values (@string) 114 115 select @count = 1 116 select @end_flag = 1 117 118 while (@end_flag != 0) 119 begin 120 select @string = substring(description, @count, 62) 121 from master.dbo.syscharsets 122 where id = @sortid and csid = @charid 123 select @end_flag = isnull(ascii(@string), 0) 124 125 if (@end_flag != 0) 126 begin 127 select @string = " " + @string 128 insert #helpsort3 values (@string) 129 end 130 131 select @count = @count + 62 132 end 133 END 134 135 /* 136 ** Insert the list of built-in and external Unilib 137 ** sort orders into syscharsets. This is a special 138 ** feature of the sortkey built-in function. 139 */ 140 select @image = sortkey(null, "all") 141 142 /* List of sort order names, ids and table names */ 143 print "" 144 145 select 146 "Collation Name" = name, 147 "Collation ID" = id 148 from 149 master.dbo.syscharsets 150 where 151 type = 2003 152 print "" 153 select 154 "Loadable Sort Table Name" = name, 155 "Collation ID" = id 156 from 157 master.dbo.syscharsets 158 where 159 type = 2004 160 161 print "" 162 163 /* 17692, "Sort Order Description" */ 164 exec sp_getmessage 17692, @msg out 165 print @msg 166 select "" = string from #helpsort3 167 168 /* 169 ** If we have a class 2 character set, then we are unable 170 ** to output the multi-byte characters in their proper 171 ** sort sequence from here... we're done. 172 */ 173 if (@csclass = 1002) 174 begin 175 return (0) 176 end 177 178 /* 179 ** Build the sorted list of printable characters for this sort order. 180 ** If it's not stored in spt_values yet, then put the results in there. 181 */ 182 if not exists 183 (select * from master.dbo.spt_values where type = 'Z' and low = @charid and high = @sortid) 184 BEGIN 185 186 create table #helpsort1(char_val char(1)) 187 188 189 190 if @charid = 0 /* ascii_8 has printable chars from 32-126 */ 191 begin 192 select @count = 32 193 while @count <= 126 194 begin 195 insert #helpsort1(char_val) 196 values (char(@count)) 197 select @count = @count + 1 198 end 199 200 end 201 202 /* iso_1 and iso15 have printable chars from 32-126 and 160-255 */ 203 if (@charid = 1 or @charid = 14) 204 begin 205 select @count = 32 206 while @count <= 126 207 begin 208 insert #helpsort1(char_val) 209 values (char(@count)) 210 select @count = @count + 1 211 end 212 213 select @count = 160 214 while @count <= 255 215 begin 216 insert #helpsort1(char_val) 217 values (char(@count)) 218 select @count = @count + 1 219 end 220 end 221 222 /* cp850 and cp858 have printable chars from 32-255 */ 223 if (@charid = 2 or @charid = 197) 224 begin 225 226 select @count = 32 227 while @count <= 255 228 begin 229 insert #helpsort1(char_val) 230 values (char(@count)) 231 select @count = @count + 1 232 end 233 end 234 235 if @charid = 3 /* cp437 has printable chars from 32-255 */ 236 begin 237 select @count = 32 238 while @count <= 255 239 begin 240 insert #helpsort1(char_val) 241 values (char(@count)) 242 select @count = @count + 1 243 end 244 end 245 246 /* roman8 and roman9 have printable chars from 32-126 and 161-254 */ 247 if (@charid = 4 or @charid = 218) 248 begin 249 select @count = 32 250 while @count <= 126 251 begin 252 insert #helpsort1(char_val) 253 values (char(@count)) 254 select @count = @count + 1 255 end 256 257 select @count = 161 258 while @count <= 254 259 begin 260 insert #helpsort1(char_val) 261 values (char(@count)) 262 select @count = @count + 1 263 end 264 end 265 266 /* mac and mac_euro have printable chars from 32-126 and 128-216 */ 267 if (@charid = 5 or @charid = 198) 268 begin 269 select @count = 32 270 while @count <= 126 271 begin 272 insert #helpsort1(char_val) 273 values (char(@count)) 274 select @count = @count + 1 275 end 276 277 select @count = 128 278 while @count <= 216 279 begin 280 insert #helpsort1(char_val) 281 values (char(@count)) 282 select @count = @count + 1 283 end 284 end 285 286 /* cp1252 has undefined chars at 127, 129, 141, 143, 144, 157 */ 287 if @charid = 13 288 begin 289 select @count = 32 290 while @count <= 255 291 begin 292 if @count not in (127, 129, 141, 143, 144, 157) 293 begin 294 insert #helpsort1(char_val) 295 values (char(@count)) 296 end 297 select @count = @count + 1 298 end 299 end 300 301 /* For character sets that are not in the above list */ 302 if @charid not in (0, 1, 2, 3, 4, 5, 13, 14, 197, 198, 218) 303 begin 304 return (0) 305 end 306 307 308 select (char_val) into #helpsort2 309 from #helpsort1 310 order by char_val 311 312 declare @char char(1) 313 declare @char_str varchar(20) 314 declare @incr int 315 declare @seq_num int 316 declare @rel char(1) 317 declare @old_char char(1) 318 319 select @char = char(0), @seq_num = 0, @count = 0, @incr = 8, @end_flag = 1 320 select @rel = " ", @old_char = NULL 321 322 set rowcount 1 323 324 while @end_flag != 0 325 begin 326 327 select @char_str = "" 328 329 /* If PST_NOTUNIQ is set, then possibly caseless */ 330 if (@status & 8 = 8) 331 while (@count < @incr and @end_flag != 0) 332 begin 333 select @char = char_val 334 from #helpsort2 335 where char_val >= @char 336 select @end_flag = @@rowcount 337 338 if (@end_flag != 0) 339 begin 340 if (@char = @old_char) 341 select @rel = "=" 342 else 343 select @rel = " " 344 345 select @old_char = @char 346 select @char_str = @char_str + @rel + @char 347 delete from #helpsort2 where ascii(@char) = ascii(char_val) 348 349 select @count = @count + 1 350 end 351 end 352 else 353 while (@count < @incr and @end_flag != 0) 354 begin 355 select @char = char_val 356 from #helpsort2 357 where char_val > @char 358 select @end_flag = @@rowcount 359 360 if (@end_flag != 0) 361 begin 362 select @char_str = @char_str + @rel + @char 363 delete from #helpsort2 where ascii(@char) = ascii(char_val) 364 365 select @count = @count + 1 366 end 367 end 368 369 select @incr = @incr + 8, @seq_num = @seq_num + 1 370 insert master.dbo.spt_values(name, number, type, low, high) 371 values (@char_str, @seq_num, 'Z', @charid, @sortid) 372 end 373 374 set rowcount 0 375 376 END 377 378 /* 379 ** Display the sorted list of characters. 380 */ 381 select @count = 1 382 select @end_flag = 1 383 select @incr = 5 384 385 truncate table #helpsort3 386 387 while @end_flag != 0 388 BEGIN 389 select @string = "" 390 391 while @count < @incr 392 begin 393 select @string = @string + substring(name, 2, 19) 394 from master.dbo.spt_values 395 where type = 'Z' and number = @count and low = @charid 396 and high = @sortid 397 select @end_flag = @@rowcount 398 select @count = @count + 1 399 end 400 401 select @incr = @incr + 4 402 403 insert into #helpsort3 values (@string) 404 END 405 406 /* 17693, "Characters, in Order" */ 407 if @charid in (0, 1, 2, 3, 4, 5, 13, 14, 197, 198, 218) 408 begin 409 exec sp_getmessage 17693, @msg out 410 print @msg 411 select "" = string from #helpsort3 412 413 end 414 415 set nocount off 416 417 return (0) 418
exec sp_procxmode 'sp_helpsort', 'AnyMode' go Grant Execute on sp_helpsort to public go
| RESULT SETS | |
| sp_helpsort_rset_002 | |
| sp_helpsort_rset_001 | |
| sp_helpsort_rset_003 | |
| sp_helpsort_rset_004 | |
| DEPENDENCIES |
| PROCS AND TABLES USED read_writes table tempdb..#helpsort3 (1) read_writes table tempdb..#helpsort1 (1) read_writes table master..spt_values (1)  reads table master..syscurconfigs (1) reads table master..syscharsets (1) read_writes table tempdb..#helpsort2 (1) calls proc sybsystemprocs..sp_getmessage  calls proc sybsystemprocs..sp_validlang reads table master..syslanguages (1) reads table master..syslanguages (1) reads table sybsystemprocs..sysusermessages reads table master..sysmessages (1) |
