DatabaseProcApplicationCreatedLinks
sybsystemprocssp_add_time_range  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3     
4     /*
5     ** Messages for "sp_add_time_range"
6     **
7     ** 17260, "Can't run %1! from within a transaction."
8     ** 17261, "Only the System Administrator (SA) may execute thi
9     s procedure."
10    ** 18182, "Timerange name must be non-NULL."
11    ** 18183, "Starting day must be non-NULL."
12    ** 18184, "Ending day must be non-NULL."
13    ** 18185, "Starting time must be non-NULL."
14    ** 18186, "Unknown startday %1!."
15    ** 18187, "Unknown endday %1!."
16    ** 18188, "Range '%1!' already exists."
17    ** 18189, "Unknown starting time value %1!."
18    ** 18190, "Unknown ending time value %1!."
19    ** 18191, "Ending time must be later in the day than starting time."
20    ** 18192, "New time range created. ID number is %1!."
21    ** 18219, "Ending time must be non-NULL."
22    ** 18773, "HA_LOG: HA consistency check failure in stored procedure '%1!' on companion server '%2!'"
23    ** 18819, "Time range '%1!' or id '%2!' already exists in systimeranges."
24    ** 18881, "Unable to generate %1! for HA use. Please refer to documentation for details."
25    */
26    
27    /* 
28    ** IMPORTANT: Please read the following instructions before
29    **   making changes to this stored procedure.
30    **
31    **	To make this stored procedure compatible with High Availability (HA),
32    **	changes to certain system tables must be propagated 
33    **	to the companion server under some conditions.
34    **	The tables include (but are not limited to):
35    **		syslogins, sysservers, sysattributes, systimeranges,
36    **		sysresourcelimits, sysalternates, sysdatabases,
37    **		syslanguages, sysremotelogins, sysloginroles,
38    **		sysalternates (master DB only), systypes (master DB only),
39    **		sysusers (master DB only), sysprotects (master DB only)
40    **	please refer to the HA documentation for detail.
41    **
42    **	Here is what you need to do: 
43    **	For each insert/update/delete statement, add three sections to
44    **	-- start HA transaction prior to the statement
45    **	-- add the statement
46    **	-- add HA synchronization code to propagate the change to the companion
47    **
48    **	For example, if you are adding 
49    **		insert master.dbo.syslogins ......
50    **	the code should look like:
51    **	1. Before that SQL statement:
52    **		
53    **	2. Now, the SQL statement:
54    **		insert master.dbo.syslogins ......
55    **	3. Add a HA synchronization section right after the SQL statement:
56    **		
57    **
58    **	You may need to do similar change for each built-in function you
59    **	want to add.
60    **
61    **	Finally, add a separate part at a place where it can not
62    **	be reached by the normal execution path:
63    **	clean_all:
64    **		
65    **		return (1)
66    */
67    
68    create or replace procedure sp_add_time_range
69        @name varchar(255), /* range name */
70        @startday varchar(30), /* first day of range */
71        @endday varchar(30), /* last day of range */
72        @starttime varchar(30), /* time of day when range begins */
73        @endtime varchar(30) /* time of day when range ends */
74    as
75    
76        declare @msg varchar(1024)
77        declare @curmaxid int
78        declare @newmaxid int
79        declare @start_dt datetime
80        declare @end_dt datetime
81        declare @startdaynum int
82        declare @enddaynum int
83        declare @curcount int
84        declare @searchcount int
85        declare @rtn_code int
86        declare @HA_CERTIFIED tinyint /* Is the SP HA certified ? */
87        declare @retstat int
88        declare @gp_enabled int
89        declare @dummy int
90        declare @nullarg varchar(1)
91    
92    
93        select @HA_CERTIFIED = 0
94    
95    
96    
97    
98        /* check to see if we are using HA specific SP for a HA enabled server */
99        exec @retstat = sp_ha_check_certified 'sp_add_time_range', @HA_CERTIFIED
100       if (@retstat != 0)
101           return (1)
102   
103       select @nullarg = NULL
104       execute @retstat = sp_aux_checkroleperm "sa_role", "manage resource limit",
105           @nullarg, @gp_enabled output
106   
107       if (@gp_enabled = 0)
108       begin
109           /* validate that this guy is really the sa */
110           if (proc_role("sa_role") = 0) return 1
111       end
112       else
113       begin
114           select @dummy = proc_auditperm("manage resource limit", @retstat)
115       end
116       if (@retstat != 0)
117       begin
118           return (1)
119       end
120   
121       if @@trancount > 0
122       begin
123           /*
124           ** 17260, "Can't run %1! from within a transaction." 
125           */
126           raiserror 17260, "sp_add_time_range"
127           return (1)
128       end
129   
130       /* Validate all parameters */
131   
132       select @name = rtrim(@name)
133   
134       if (@name is null)
135       begin
136           /*
137           ** 18182, "Timerange name must be non-NULL."
138           */
139           raiserror 18182
140           return (1)
141       end
142   
143       if (@startday is null)
144       begin
145           /*
146           ** 18183, "Starting day must be non-NULL."
147           */
148           raiserror 18183
149           return (1)
150       end
151   
152       if (@endday is null)
153       begin
154           /*
155           ** 18184, "Ending day must be non-NULL."
156           */
157           raiserror 18184
158           return (1)
159       end
160   
161       if (@starttime is null)
162       begin
163           /*
164           ** 18185, "Starting time must be non-NULL."
165           */
166           raiserror 18185
167           return (1)
168       end
169   
170       if (@endtime is null)
171       begin
172           /*
173           ** 18219, "Ending time must be non-NULL."
174           */
175           raiserror 18219
176           return (1)
177       end
178   
179       if exists (select *
180               from master.dbo.systimeranges
181               where upper(name) = upper(@name))
182       begin
183           /*
184           ** 18188, "Range '%1!' already exists."
185           */
186           raiserror 18188, @name
187           return (1)
188       end
189   
190       /* Get the start day's number based on the day name */
191       select @startdaynum = daytonum(@startday)
192   
193       if (@startdaynum = - 1)
194       begin
195           /*
196           ** 18186, "Unknown startday %1!."
197           */
198           raiserror 18186, @startday
199           return (1)
200       end
201   
202       /* Get the end day's number based on the day name */
203       select @enddaynum = daytonum(@endday)
204   
205       if (@enddaynum = - 1)
206       begin
207           /*
208           ** 18187, "Unknown endday %1!."
209           */
210           raiserror 18187, @endday
211           return (1)
212       end
213   
214       /* Make sure the time params are valid times */
215       select @start_dt = convert(datetime, @starttime)
216       select @end_dt = convert(datetime, @endtime)
217       if (@start_dt is null)
218       begin
219           /*
220           ** 18189, "Unknown starting time value %1!."
221           */
222           raiserror 18189, @starttime
223           return (1)
224       end
225       if (@end_dt is null)
226       begin
227           /*
228           ** 18190, "Unknown ending time value %1!."
229           */
230           raiserror 18190, @endtime
231           return (1)
232       end
233   
234       /* Make sure the time params are in the right order.
235       ** The exception is when endtime is 00:00, which can
236       ** be taken to mean midnight.
237       */
238       if ((datepart(hour, @end_dt) != 0) or
239               (datepart(minute, @end_dt) != 0) or
240               (datepart(second, @end_dt) != 0)) and
241           (@start_dt > @end_dt)
242       begin
243           /*
244           ** 18191, "Ending time must be later in the day than starting time."
245           */
246           raiserror 18191
247           return (1)
248       end
249   
250       exec @rtn_code = sp_gen_timerange_id @newmaxid output
251       if (@rtn_code != 0)
252           return (1)
253   
254   
255   
256       /* Insert! */
257       insert master.dbo.systimeranges values (@name, @newmaxid, @startdaynum, @enddaynum, @starttime, @endtime)
258   
259   
260   
261       dbcc waketimerange
262   
263       /*
264       ** 18192, "New time range created. ID number is %1!."
265       */
266       exec sp_getmessage 18192, @msg output
267       print @msg, @newmaxid
268       return (0)
269   
270   clean_all:
271   
272   
273   
274       return (1)
275   


exec sp_procxmode 'sp_add_time_range', 'AnyMode'
go

Grant Execute on sp_add_time_range to public
go
DEFECTS
 MURC 6 Unreachable Code 270
 MURC 6 Unreachable Code 274
 MTYP 4 Assignment type mismatch endday: tinyint = int 257
 MTYP 4 Assignment type mismatch endtime: varchar(10) = varchar(30) 257
 MTYP 4 Assignment type mismatch id: smallint = int 257
 MTYP 4 Assignment type mismatch startday: tinyint = int 257
 MTYP 4 Assignment type mismatch starttime: varchar(10) = varchar(30) 257
 TNOU 4 Table with no unique index master..systimeranges master..systimeranges
 MGTP 3 Grant to public master..systimeranges  
 MGTP 3 Grant to public sybsystemprocs..sp_add_time_range  
 MNER 3 No Error Check should check @@error after insert 257
 MNER 3 No Error Check should check return value of exec 266
 MUCO 3 Useless Code Useless Brackets 100
 MUCO 3 Useless Code Useless Brackets 101
 MUCO 3 Useless Code Useless Brackets 107
 MUCO 3 Useless Code Useless Brackets 110
 MUCO 3 Useless Code Useless Brackets 116
 MUCO 3 Useless Code Useless Brackets 118
 MUCO 3 Useless Code Useless Brackets 127
 MUCO 3 Useless Code Useless Brackets 134
 MUCO 3 Useless Code Useless Brackets 140
 MUCO 3 Useless Code Useless Brackets 143
 MUCO 3 Useless Code Useless Brackets 149
 MUCO 3 Useless Code Useless Brackets 152
 MUCO 3 Useless Code Useless Brackets 158
 MUCO 3 Useless Code Useless Brackets 161
 MUCO 3 Useless Code Useless Brackets 167
 MUCO 3 Useless Code Useless Brackets 170
 MUCO 3 Useless Code Useless Brackets 176
 MUCO 3 Useless Code Useless Brackets 187
 MUCO 3 Useless Code Useless Brackets 193
 MUCO 3 Useless Code Useless Brackets 199
 MUCO 3 Useless Code Useless Brackets 205
 MUCO 3 Useless Code Useless Brackets 211
 MUCO 3 Useless Code Useless Brackets 217
 MUCO 3 Useless Code Useless Brackets 223
 MUCO 3 Useless Code Useless Brackets 225
 MUCO 3 Useless Code Useless Brackets 231
 MUCO 3 Useless Code Useless Brackets 247
 MUCO 3 Useless Code Useless Brackets 251
 MUCO 3 Useless Code Useless Brackets 252
 MUCO 3 Useless Code Useless Brackets 268
 MUCO 3 Useless Code Useless Brackets 274
 VNRD 3 Variable is not read @dummy 114
 VUNU 3 Variable is not used @curmaxid 77
 VUNU 3 Variable is not used @curcount 83
 VUNU 3 Variable is not used @searchcount 84
 MSUB 2 Subquery Marker 179
 MTR1 2 Metrics: Comments Ratio Comments: 57% 68
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 8 = 21dec - 15exi + 2 68
 MTR3 2 Metrics: Query Complexity Complexity: 102 68

DATA PROPAGATION detailed
ColumnWritten To
@enddaysystimeranges.endday  
@endtimesystimeranges.endtime  
@namesystimeranges.name  
@startdaysystimeranges.startday  
@starttimesystimeranges.starttime  

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_getmessage  
   reads table master..syslanguages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..sysmessages (1)  
   reads table sybsystemprocs..sysusermessages  
read_writes table master..systimeranges (1)  
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..sysconfigures (1)  
   reads table master..syscurconfigs (1)  
calls proc sybsystemprocs..sp_ha_check_certified  
   reads table tempdb..sysobjects (1)  
calls proc sybsystemprocs..sp_gen_timerange_id  
   reads table master..systimeranges (1)