DatabaseProcApplicationCreatedLinks
sybsystemprocssp_helpsort  14 déc. 14Defects Propagation 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 or replace 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_004
sp_helpsort_rset_002
sp_helpsort_rset_001
sp_helpsort_rset_003

DEFECTS
 MEST 4 Empty String will be replaced by Single Space 143
 MEST 4 Empty String will be replaced by Single Space 152
 MEST 4 Empty String will be replaced by Single Space 161
 MEST 4 Empty String will be replaced by Single Space 166
 MEST 4 Empty String will be replaced by Single Space 327
 MEST 4 Empty String will be replaced by Single Space 389
 MEST 4 Empty String will be replaced by Single Space 411
 MTYP 4 Assignment type mismatch @sortid: tinyint = int 40
 MTYP 4 Assignment type mismatch @charid: tinyint = int 43
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
183
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 40
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 43
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 77
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 88
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 151
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 159
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs tinyint 183
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs tinyint 395
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs tinyint 396
 TNOI 4 Table with no index master..syscurconfigs master..syscurconfigs
 TNOU 4 Table with no unique index master..spt_values master..spt_values
 MAW1 3 Warning message on %name% master..syscharsets.id: Warning message on syscharsets 47
 MAW1 3 Warning message on %name% master..syscharsets.id: Warning message on syscharsets 74
 MAW1 3 Warning message on %name% master..syscharsets.id: Warning message on syscharsets 77
 MAW1 3 Warning message on %name% master..syscharsets.id: Warning message on syscharsets 88
 MAW1 3 Warning message on %name% master..syscharsets.id: Warning message on syscharsets 109
 MAW1 3 Warning message on %name% master..syscharsets.id: Warning message on syscharsets 111
 MAW1 3 Warning message on %name% master..syscharsets.id: Warning message on syscharsets 122
 MAW1 3 Warning message on %name% master..syscharsets.id: Warning message on syscharsets 147
 MAW1 3 Warning message on %name% master..syscharsets.id: Warning message on syscharsets 155
 MGTP 3 Grant to public master..spt_values  
 MGTP 3 Grant to public master..syscharsets  
 MGTP 3 Grant to public master..syscurconfigs  
 MGTP 3 Grant to public sybsystemprocs..sp_helpsort  
 MNER 3 No Error Check should check return value of exec 58
 MNER 3 No Error Check should check @@error after insert 59
 MNER 3 No Error Check should check @@error after insert 60
 MNER 3 No Error Check should check return value of exec 62
 MNER 3 No Error Check should check @@error after insert 63
 MNER 3 No Error Check should check @@error after insert 64
 MNER 3 No Error Check should check return value of exec 73
 MNER 3 No Error Check should check @@error after insert 79
 MNER 3 No Error Check should check @@error after insert 94
 MNER 3 No Error Check should check return value of exec 103
 MNER 3 No Error Check should check @@error after insert 104
 MNER 3 No Error Check should check return value of exec 108
 MNER 3 No Error Check should check @@error after insert 113
 MNER 3 No Error Check should check @@error after insert 128
 MNER 3 No Error Check should check return value of exec 164
 MNER 3 No Error Check should check @@error after insert 195
 MNER 3 No Error Check should check @@error after insert 208
 MNER 3 No Error Check should check @@error after insert 216
 MNER 3 No Error Check should check @@error after insert 229
 MNER 3 No Error Check should check @@error after insert 240
 MNER 3 No Error Check should check @@error after insert 252
 MNER 3 No Error Check should check @@error after insert 260
 MNER 3 No Error Check should check @@error after insert 272
 MNER 3 No Error Check should check @@error after insert 280
 MNER 3 No Error Check should check @@error after insert 294
 MNER 3 No Error Check should check @@error after select into 308
 MNER 3 No Error Check should check @@error after delete 347
 MNER 3 No Error Check should check @@error after delete 363
 MNER 3 No Error Check should check @@error after insert 370
 MNER 3 No Error Check should check @@error after truncate 385
 MNER 3 No Error Check should check @@error after insert 403
 MNER 3 No Error Check should check return value of exec 409
 MUCO 3 Useless Code Useless Brackets 54
 MUCO 3 Useless Code Useless Brackets 84
 MUCO 3 Useless Code Useless Brackets 91
 MUCO 3 Useless Code Useless Brackets 100
 MUCO 3 Useless Code Useless Brackets 118
 MUCO 3 Useless Code Useless Brackets 125
 MUCO 3 Useless Code Useless Brackets 173
 MUCO 3 Useless Code Useless Brackets 175
 MUCO 3 Useless Code Useless Brackets 203
 MUCO 3 Useless Code Useless Brackets 223
 MUCO 3 Useless Code Useless Brackets 247
 MUCO 3 Useless Code Useless Brackets 267
 MUCO 3 Useless Code Useless Brackets 304
 MUCO 3 Useless Code Useless Brackets 308
 MUCO 3 Useless Code Useless Brackets 330
 MUCO 3 Useless Code Useless Brackets 331
 MUCO 3 Useless Code Useless Brackets 338
 MUCO 3 Useless Code Useless Brackets 340
 MUCO 3 Useless Code Useless Brackets 353
 MUCO 3 Useless Code Useless Brackets 360
 MUCO 3 Useless Code Useless Brackets 417
 MUIN 3 Column created using implicit nullability 34
 MUIN 3 Column created using implicit nullability 186
 QAFM 3 Var Assignment from potentially many rows 40
 QAFM 3 Var Assignment from potentially many rows 43
 QAFM 3 Var Assignment from potentially many rows 74
 QAFM 3 Var Assignment from potentially many rows 86
 QAFM 3 Var Assignment from potentially many rows 393
 QCRS 3 Conditional Result Set 411
 QCTC 3 Conditional Table Creation 186
 QCTC 3 Conditional Table Creation 308
 QISO 3 Set isolation level 32
 QIWC 3 Insert with not all columns specified missing 2 columns out of 7 370
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscharsets.csyscharsets unique clustered
(id, csid)
Intersection: {id}
77
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscharsets.csyscharsets unique clustered
(id, csid)
Intersection: {id}
88
 QSWV 3 Sarg with variable @charid, Candidate Index: spt_values.spt_valuesclust clustered(number, type) U 183
 QSWV 3 Sarg with variable @sortid, Candidate Index: spt_values.spt_valuesclust clustered(number, type) U 183
 QSWV 3 Sarg with variable @charid, Candidate Index: spt_values.spt_valuesclust clustered(number, type) S 395
 QSWV 3 Sarg with variable @count, Candidate Index: spt_values.spt_valuesclust clustered(number, type) S 395
 QSWV 3 Sarg with variable @sortid, Candidate Index: spt_values.spt_valuesclust clustered(number, type) S 396
 VNRD 3 Variable is not read @image 140
 MRST 2 Result Set Marker 145
 MRST 2 Result Set Marker 153
 MRST 2 Result Set Marker 166
 MRST 2 Result Set Marker 411
 MSUB 2 Subquery Marker 183
 MTR1 2 Metrics: Comments Ratio Comments: 17% 13
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 54 = 55dec - 3exi + 2 13
 MTR3 2 Metrics: Query Complexity Complexity: 278 13

DEPENDENCIES
PROCS AND TABLES USED
read_writes table tempdb..#helpsort2 (1) 
read_writes table tempdb..#helpsort1 (1) 
reads table master..syscurconfigs (1)  
read_writes table tempdb..#helpsort3 (1) 
calls proc sybsystemprocs..sp_getmessage  
   reads table master..sysmessages (1)  
   reads table sybsystemprocs..sysusermessages  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..syslanguages (1)  
writes table sybsystemprocs..sp_helpsort_rset_003 
reads table master..syscharsets (1)  
writes table sybsystemprocs..sp_helpsort_rset_001 
read_writes table master..spt_values (1)  
writes table sybsystemprocs..sp_helpsort_rset_004 
writes table sybsystemprocs..sp_helpsort_rset_002