DatabaseProcApplicationCreatedLinks
sybsystemprocssp_versioncrack  14 déc. 14Defects Propagation Dependencies

1     
2     /*
3     **	SP_VERSIONCRACK
4     **
5     **	A utility procedure to parse a version string and extract desired
6     **	information from it and return it to the caller.
7     **
8     **	Parameter
9     **		@str 	    - the version string to be parsed.
10    **		@keywordstr - the keyword defining what information is to be
11    **			      extracted.
12    **
13    **	Returns
14    **		- the desired value/information string.
15    **		- the desired value int.
16    **
17    **		0 - if all goes well.
18    **		1 - if any error while parsing. 
19    **
20    {
21    */
22    create or replace procedure sp_versioncrack
23    (
24        @str varchar(255)
25        , @keywordstr varchar(30)
26        , @outstr varchar(30) OUT
27        , @outint int OUT
28    )
29    as
30        begin -- {
31    
32            declare @esdpattern varchar(10)
33                , @ebfpattern varchar(10)
34                , @sppattern varchar(10)
35                , @plpattern varchar(10)
36                , @pattern varchar(30)
37                , @esdkeyword varchar(4)
38                , @ebfkeyword varchar(4)
39                , @spkeyword varchar(4)
40                , @plkeyword varchar(4)
41                , @leftstr varchar(255)
42                , @rightstr varchar(255)
43                , @spaceindex int
44                , @slashindex int
45                , @stopatindex int
46                , @retval int
47                , @tempfloat float
48    
49            select @outstr = NULL
50                , @outint = - 1
51                , @esdpattern = "ESD#"
52                , @ebfpattern = "EBF "
53                , @sppattern = "SP"
54                , @plpattern = "PL"
55                , @esdkeyword = "ESD"
56                , @ebfkeyword = "EBF"
57                , @spkeyword = "SP"
58                , @plkeyword = "PL"
59                , @keywordstr = upper(ltrim(rtrim(@keywordstr)))
60                , @spaceindex = 0
61                , @slashindex = 0
62    
63            /*
64            ** If @str is NULL or @keywordstr is NULL, sp_split_string will return
65            ** NULL. So, need not handle that case.
66            */
67    
68            select @pattern = case @keywordstr
69                    when @ebfkeyword then @ebfpattern
70                    when @esdkeyword then @esdpattern
71                    when @spkeyword then @sppattern
72                    when @plkeyword then @plpattern
73                    else NULL
74                end
75    
76            if @pattern = NULL -- Invalid keyword string.	
77                return 1
78    
79            if (@pattern = @sppattern) OR (@pattern = @plpattern)
80            begin
81                select @outint = 0
82                select @outstr = convert(varchar, 0)
83            end
84    
85            -- Extract the requested value form @str.
86            exec @retval = sp_split_string @str, @pattern, 0, @leftstr out
87                , @rightstr out
88    
89    
90            if @retval = 1
91            begin
92                -- If keyword was ESD, try again using SP for the new (2013) 
93                -- servicepack naming convention.
94                -- We are not expecting that an ESD version string is passed in 
95                -- to the stored proc and we look for an SP pattern therein.
96                if @keywordstr = @esdkeyword
97                begin
98                    select @pattern = @sppattern
99                end
100               else if ((@keywordstr = @spkeyword)
101                       OR (@keywordstr = @plkeyword))
102               begin
103                   select @pattern = @esdpattern
104               end
105   
106               if ((@pattern = @sppattern) OR (@pattern = @esdpattern))
107               begin
108                   -- Extract the requested value form @str.
109                   exec @retval = sp_split_string @str, @pattern, 0
110                       , @leftstr out
111                       , @rightstr out
112               end
113   
114               if @retval = 1
115               begin
116                   -- The keyword pattern not found in @str. Or 
117                   -- @str is NULL.
118                   return 1
119               end
120           end
121   
122           select @spaceindex = charindex(' ', @rightstr)
123           select @slashindex = charindex('/', @rightstr)
124   
125           select @stopatindex = case
126                   when @spaceindex = 0 then @slashindex - 1
127                   when @slashindex = 0 then @spaceindex - 1
128                   when @spaceindex < @slashindex
129                   then @spaceindex - 1
130                   else @slashindex - 1
131               end
132   
133           if @stopatindex <= 0
134           begin
135               -- No space, nor slash, found in @rightstr.
136               -- At this moment, for ESD or EBF, this can not be so, so return
137               -- with error.
138               --
139               return 1
140           end
141   
142           select @outstr = substring(@rightstr, 1, @stopatindex)
143   
144           if @outstr is not NULL
145           begin
146   
147               -- Check if it is an integer value, if so, get the value. The
148               -- check for '$' is added because isnumeric will return 1 for
149               -- money data type also. 
150               --
151               if ((isnumeric(@outstr) = 1) and (charindex('$', @outstr) = 0))
152               begin
153                   select @outint =
154                       convert(int, convert(numeric(6, 0), @outstr))
155   
156                   if ((@keywordstr = @plkeyword)
157                           AND (@pattern = @esdpattern))
158                   begin
159                       select @tempfloat = convert(float, @outstr)
160                       select @tempfloat = (@tempfloat - @outint)
161                       if (@tempfloat < 0)
162                           select @tempfloat = (1.0 + @tempfloat)
163                       select @outint =
164                           convert(int, (@tempfloat * 10))
165                   end
166   
167                   select @outstr = convert(varchar(30), @outint)
168               end
169           end
170   
171           return 0
172   
173       end -- }	-- }
174   


exec sp_procxmode 'sp_versioncrack', 'AnyMode'
go

Grant Execute on sp_versioncrack to public
go
DEFECTS
 MGTP 3 Grant to public sybsystemprocs..sp_versioncrack  
 MNAC 3 Not using ANSI 'is null' 76
 MUCO 3 Useless Code Useless Brackets in create proc 23
 MUCO 3 Useless Code Useless Begin-End Pair 30
 MUCO 3 Useless Code Useless Brackets 100
 MUCO 3 Useless Code Useless Brackets 106
 MUCO 3 Useless Code Useless Brackets 151
 MUCO 3 Useless Code Useless Brackets 156
 MUCO 3 Useless Code Useless Brackets 161
 MUCO 3 Useless Code Useless Brackets 164
 VNRD 3 Variable is not read @leftstr 110
 MTR1 2 Metrics: Comments Ratio Comments: 28% 22
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 17 = 18dec - 3exi + 2 22
 MTR3 2 Metrics: Query Complexity Complexity: 56 22

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_split_string  

CALLERS
called by proc sybsystemprocs..sp_spaceusage_object_init  
   called by proc sybsystemprocs..sp_spaceusage_object  
      called by proc sybsystemprocs..sp_spaceusage  
called by proc sybsystemprocs..sp_mon_archive_genSQL  
   called by proc sybsystemprocs..sp_monitor_deadlock  
      called by proc sybsystemprocs..sp_monitor  
   called by proc sybsystemprocs..sp_mon_archive_monTable  
      called by proc sybsystemprocs..sp_mon_archive_deadlock  
called by proc sybsystemprocs..sp_spaceusage_tranlog_init  
   called by proc sybsystemprocs..sp_spaceusage_tranlog  
      called by proc sybsystemprocs..sp_spaceusage