DatabaseProcApplicationCreatedLinks
sybsystemprocssp_version  31 Aug 14Defects Dependencies

1     
2     /*
3     ** Generated by spgenmsgs.pl on Thu Sep 16 22:53:10 2004 
4     */
5     /*
6     ** raiserror Messages for version [Total 6]
7     **
8     ** 18524, "%1!: Permission denied. This operation requires System Administrator (sa_role) role."
9     ** 19194, "Argument '%1!' is either invalid or non-unique. Valid arguments are: %2!"
10    ** 19378, "Delete from the table %1! affected %2! rows but expected %3! rows to be deleted. Command aborted."
11    ** 19379, "Update of the table %1! affected %2! rows but expected %3! rows to be updated. Command aborted."
12    ** 19380, "Error in accessing the table %1!."
13    ** 19381, "Invalid argument to %1!. It requires a non-null value."
14    */
15    /*
16    ** sp_getmessage Messages for version [Total 0]
17    */
18    /*
19    ** End spgenmsgs.pl output.
20    */
21    /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
22    /*      5.0     1.0     09/10/04      sproc/src/version */
23    
24    /*
25    ** This procedure SP_VERSION is used to find the
26    ** version of the install scripts. In addition to 
27    ** this it also gives the details of the successfull
28    ** installation such as begining and ending Date with
29    ** Time of the installation.
30    **
31    ** This procedure uses the master.dbo.sysattributes
32    ** to store all the required information. The
33    ** columns used for storing the information is
34    ** as follows
35    **
36    **	Column Name	Information stored
37    **	------------	------------------
38    **	object_cinfo 	Script file name
39    **	char_value	Version String
40    **	comments	Date and Time info.
41    **	int_value	Code value
42    **			(used internally)
43    **	
44    ** In sysattributes this procedure will access the rows
45    ** defined under the class 23.
46    **	
47    ** Usage:
48    ** ------
49    ** sp_version [@Scriptfile [, @verbose [, @version [, @code ] ] ] ]
50    **
51    ** Returns:
52    **	 0	 - Successful execution.
53    **	 1	 - Invalid options.
54    **	 2	 - Insert/Update failures.
55    **
56    */
57    
58    create procedure sp_version
59        @script_file varchar(255) = NULL,
60        @verbose varchar(3) = NULL,
61        @version varchar(255) = NULL,
62        @code varchar(5) = NULL
63    as
64    
65        declare @class int
66            , @check_exist int
67            , @date varchar(150)
68            , @start_date varchar(150)
69            , @end_date varchar(150)
70            , @featurecode varchar(2)
71            , @attr_code int
72            , @loc_error int
73            , @loc_rowcount int
74    
75        select @class = 23
76            , @check_exist = 0
77            , @start_date = NULL
78            , @end_date = NULL
79            , @featurecode = 'sv'
80            , @attr_code = 2 -- randomly choosen for this feature
81    
82        /*
83        ** If code = NULL means it is in reportin mode.
84        */
85    
86        if (@code IS NULL)
87        begin --{
88            /*
89            ** Check whether the information is for
90            ** a specific install script. If so return
91            ** the required information. Otherwise return
92            ** all the rows with class = 23.
93            */
94            if (@version IS NULL)
95            begin --{
96                select object_cinfo as Script, char_value as Version,
97                    comments as "Start_End_Date",
98                    case int_value
99                        when 0 then 'Incomplete'
100                       else 'Complete'
101                   end as "Status"
102               into #sysattributes
103               from master.dbo.sysattributes
104               where class = @class and object_type = @featurecode
105                   and attribute = @attr_code
106                   and (@script_file IS NULL
107                       or object_cinfo like @script_file)
108   
109               /*
110               ** If verbose option is set then print all the information 
111               ** including the date and time information.
112               */
113   
114               if (@verbose IS NULL)
115               begin
116                   exec sp_autoformat #sysattributes,
117                       "Script, Version, Status", null,
118                       "ORDER BY 1"
119               end
120               else if (@verbose = 'all')
121               begin
122                   exec sp_autoformat #sysattributes,
123                       "Script, Version, Status, 
124   					'Start/End Date' = Start_End_Date",
125                       null, "ORDER BY 1"
126               end
127               else
128               begin
129                   raiserror 19194, "verbose", "all"
130                   return (1)
131               end
132           end --}
133           else
134           begin --{
135               /* 
136               ** version is non-null means it is updating / insert mode
137               ** raise appropriate error messages for script file and
138               ** code value.
139               */
140               if (@script_file IS NULL)
141                   raiserror 19381, "script_file"
142               raiserror 19194, 'code', "'start', 'end'."
143               return (1)
144           end --}
145           return (0)
146       end --}
147       else if (@version IS NULL or @script_file IS NULL)
148       begin
149           /*
150           ** If code is not NULL then check if script_file or version is NULL.
151           ** If any of them are NULL then give an appropriate error message.
152           */
153           if (@version IS NULL)
154               raiserror 19381, "version"
155           if (@script_file IS NULL)
156               raiserror 19381, "script_file"
157           if (@code != 'start' and @code != 'end')
158               raiserror 19194, 'code', "'start', 'end'"
159           return (1)
160       end
161       else
162       begin
163           /*
164           ** If all the above conditions are satisfied and code is not NULL
165           ** then we need to insert / update the version string. To do this
166           ** check whether the use has 'sa_role'.
167           */
168           if (proc_role("sa_role") = 0)
169           begin
170               raiserror 18524, "sp_version"
171               return (1)
172           end
173       end
174   
175       if (@code = 'start')
176       begin --{
177           /*
178           ** code = 'start' means the begining of the installation (install
179           ** script). Before adding a new row to sysattributes check
180           ** for the previous rows and take appropriate action.
181           */
182   
183           begin tran update_version_string
184   
185           delete from master.dbo.sysattributes
186           where class = @class and object_type = @featurecode
187               and attribute = @attr_code
188               and object_cinfo = @script_file
189   
190           select @loc_error = @@error, @loc_rowcount = @@rowcount
191   
192           if (@loc_error != 0) --{
193           begin
194               raiserror 19380, "sysattributes"
195               rollback update_version_string
196               return (2)
197           end --}
198   
199           if (@loc_rowcount > 1) --{
200           begin
201               raiserror 19378, "sysattributes", @@rowcount, "1"
202               rollback update_version_string
203               return (2)
204           end --}
205   
206           insert into master.dbo.sysattributes(class, object_type,
207               attribute, object_cinfo, char_value, int_value, comments)
208           values (@class, @featurecode, @attr_code, @script_file,
209               @version, 0, "[Started=" + convert(varchar, getdate()) + "]")
210   
211           if (@@error != 0)
212           begin
213               raiserror 19380, "sysattributes"
214               rollback update_version_string
215               return (2)
216           end
217   
218           commit tran update_version_string
219           return (0)
220       end --}
221       else if (@code = 'end')
222       begin --{
223           /*
224           ** @code = 'end' represents the successfull installation
225           ** of the script. Change int_value to reflect this
226           ** Before updating check whether the script is 
227           ** is registered for the installation. If so update
228           ** row for the script file with int_value = 0.
229           ** Otherwise raise an error.
230           */
231   
232           begin tran complete_version_string
233   
234           /*
235           ** The script is registered at the begining of the
236           ** Installtaion.
237           */
238   
239           select @date = convert(varchar, getdate())
240   
241           select @start_date = comments
242           from master.dbo.sysattributes
243           where class = @class and attribute = @attr_code
244               and object_type = @featurecode
245               and object_cinfo = @script_file
246   
247   
248           if (@start_date IS NOT NULL) --{
249           begin
250               select @end_date = @start_date
251                   + '-[Completed=' + @date + ']'
252   
253               update master.dbo.sysattributes
254               set int_value = 1, comments = @end_date
255               where class = @class and attribute = @attr_code
256                   and object_type = @featurecode
257                   and object_cinfo = @script_file
258   
259               select @loc_error = @@error, @loc_rowcount = @@rowcount
260   
261               if (@loc_error != 0) --{	
262               begin
263                   raiserror 19380, "sysattributes"
264                   rollback complete_version_string
265                   return (2)
266               end --}
267   
268               if (@loc_rowcount > 1) --{	
269               begin
270                   raiserror 19379, "sysattributes", @@rowcount, "1"
271                   rollback complete_version_string
272                   return (2)
273               end --}
274           end --}
275           else
276           begin --{	
277   
278               select @end_date = "[Completed=" + @date + "]"
279   
280               insert into master.dbo.sysattributes
281               (class, object_type, attribute, object_cinfo, char_value,
282                   int_value, comments)
283               values (@class, @featurecode, @attr_code, @script_file,
284                   @version, 1, @end_date)
285   
286               if (@@error != 0) --{
287               begin
288                   raiserror 19380, "sysattributes"
289                   rollback complete_version_string
290                   return (2)
291               end --}
292   
293           end --}
294   
295           commit tran complete_version_string
296           return (0)
297       end --}
298       else
299       begin
300           raiserror 19194, 'code', "'start', 'end'"
301           return (1)
302       end
303   


exec sp_procxmode 'sp_version', 'AnyMode'
go

Grant Execute on sp_version to public
go
DEFECTS
 MCTR 4 Conditional Begin Tran or Commit Tran 183
 MCTR 4 Conditional Begin Tran or Commit Tran 218
 MCTR 4 Conditional Begin Tran or Commit Tran 232
 MCTR 4 Conditional Begin Tran or Commit Tran 295
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 MTYP 4 Assignment type mismatch attribute: smallint = int 208
 MTYP 4 Assignment type mismatch class: smallint = int 208
 MTYP 4 Assignment type mismatch @start_date: varchar(150) = varchar(255) 241
 MTYP 4 Assignment type mismatch attribute: smallint = int 283
 MTYP 4 Assignment type mismatch class: smallint = int 283
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 116
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 122
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 104
 QTYP 4 Comparison type mismatch smallint = int 104
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 105
 QTYP 4 Comparison type mismatch smallint = int 105
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 186
 QTYP 4 Comparison type mismatch smallint = int 186
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 187
 QTYP 4 Comparison type mismatch smallint = int 187
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 243
 QTYP 4 Comparison type mismatch smallint = int 243
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 255
 QTYP 4 Comparison type mismatch smallint = int 255
 MGTP 3 Grant to public master..sysattributes  
 MGTP 3 Grant to public sybsystemprocs..sp_version  
 MNER 3 No Error Check should check @@error after select into 96
 MNER 3 No Error Check should check return value of exec 116
 MNER 3 No Error Check should check return value of exec 122
 MUCO 3 Useless Code Useless Brackets 86
 MUCO 3 Useless Code Useless Brackets 94
 MUCO 3 Useless Code Useless Brackets 114
 MUCO 3 Useless Code Useless Brackets 120
 MUCO 3 Useless Code Useless Brackets 130
 MUCO 3 Useless Code Useless Brackets 140
 MUCO 3 Useless Code Useless Brackets 143
 MUCO 3 Useless Code Useless Brackets 145
 MUCO 3 Useless Code Useless Brackets 147
 MUCO 3 Useless Code Useless Brackets 153
 MUCO 3 Useless Code Useless Brackets 155
 MUCO 3 Useless Code Useless Brackets 157
 MUCO 3 Useless Code Useless Brackets 159
 MUCO 3 Useless Code Useless Brackets 168
 MUCO 3 Useless Code Useless Brackets 171
 MUCO 3 Useless Code Useless Brackets 175
 MUCO 3 Useless Code Useless Brackets 192
 MUCO 3 Useless Code Useless Brackets 196
 MUCO 3 Useless Code Useless Brackets 199
 MUCO 3 Useless Code Useless Brackets 203
 MUCO 3 Useless Code Useless Brackets 211
 MUCO 3 Useless Code Useless Brackets 215
 MUCO 3 Useless Code Useless Brackets 219
 MUCO 3 Useless Code Useless Brackets 221
 MUCO 3 Useless Code Useless Brackets 248
 MUCO 3 Useless Code Useless Brackets 261
 MUCO 3 Useless Code Useless Brackets 265
 MUCO 3 Useless Code Useless Brackets 268
 MUCO 3 Useless Code Useless Brackets 272
 MUCO 3 Useless Code Useless Brackets 286
 MUCO 3 Useless Code Useless Brackets 290
 MUCO 3 Useless Code Useless Brackets 296
 MUCO 3 Useless Code Useless Brackets 301
 QAFM 3 Var Assignment from potentially many rows 241
 QCTC 3 Conditional Table Creation 96
 QIWC 3 Insert with not all columns specified missing 8 columns out of 15 206
 QIWC 3 Insert with not all columns specified missing 8 columns out of 15 281
 QPNC 3 No column in condition 106
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_type, object_cinfo, attribute, class}
104
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_type, object_cinfo, attribute, class}
186
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_type, object_cinfo, attribute, class}
243
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_type, object_cinfo, attribute, class}
255
 VNRD 3 Variable is not read @check_exist 76
 MTR1 2 Metrics: Comments Ratio Comments: 41% 58
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 15 = 28dec - 15exi + 2 58
 MTR3 2 Metrics: Query Complexity Complexity: 118 58

DEPENDENCIES
PROCS AND TABLES USED
read_writes table master..sysattributes (1)  
calls proc sybsystemprocs..sp_autoformat  
   calls proc sybsystemprocs..sp_namecrack  
   reads table tempdb..systypes (1)  
   reads table tempdb..syscolumns (1)  
   reads table master..systypes (1)  
   calls proc sybsystemprocs..sp_autoformat  
   read_writes table tempdb..#colinfo_af (1) 
   reads table master..syscolumns (1)  
writes table tempdb..#sysattributes (1)