| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_add_time_range | | 31 Aug 14 | Defects 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 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 89 90 select @HA_CERTIFIED = 0 91 92 93 94 95 /* check to see if we are using HA specific SP for a HA enabled server */ 96 exec @retstat = sp_ha_check_certified 'sp_add_time_range', @HA_CERTIFIED 97 if (@retstat != 0) 98 return (1) 99 100 if (proc_role("sa_role") = 0) 101 begin 102 /* 103 ** 17261, "Only the System Administrator (SA) may execute this procedure." 104 */ 105 raiserror 17261 106 return (1) 107 end 108 109 if @@trancount > 0 110 begin 111 /* 112 ** 17260, "Can't run %1! from within a transaction." 113 */ 114 raiserror 17260, "sp_add_time_range" 115 return (1) 116 end 117 118 /* Validate all parameters */ 119 120 select @name = rtrim(@name) 121 122 if (@name is null) 123 begin 124 /* 125 ** 18182, "Timerange name must be non-NULL." 126 */ 127 raiserror 18182 128 return (1) 129 end 130 131 if (@startday is null) 132 begin 133 /* 134 ** 18183, "Starting day must be non-NULL." 135 */ 136 raiserror 18183 137 return (1) 138 end 139 140 if (@endday is null) 141 begin 142 /* 143 ** 18184, "Ending day must be non-NULL." 144 */ 145 raiserror 18184 146 return (1) 147 end 148 149 if (@starttime is null) 150 begin 151 /* 152 ** 18185, "Starting time must be non-NULL." 153 */ 154 raiserror 18185 155 return (1) 156 end 157 158 if (@endtime is null) 159 begin 160 /* 161 ** 18219, "Ending time must be non-NULL." 162 */ 163 raiserror 18219 164 return (1) 165 end 166 167 if exists (select * 168 from master.dbo.systimeranges 169 where upper(name) = upper(@name)) 170 begin 171 /* 172 ** 18188, "Range '%1!' already exists." 173 */ 174 raiserror 18188, @name 175 return (1) 176 end 177 178 /* Get the start day's number based on the day name */ 179 select @startdaynum = daytonum(@startday) 180 181 if (@startdaynum = - 1) 182 begin 183 /* 184 ** 18186, "Unknown startday %1!." 185 */ 186 raiserror 18186, @startday 187 return (1) 188 end 189 190 /* Get the end day's number based on the day name */ 191 select @enddaynum = daytonum(@endday) 192 193 if (@enddaynum = - 1) 194 begin 195 /* 196 ** 18187, "Unknown endday %1!." 197 */ 198 raiserror 18187, @endday 199 return (1) 200 end 201 202 /* Make sure the time params are valid times */ 203 select @start_dt = convert(datetime, @starttime) 204 select @end_dt = convert(datetime, @endtime) 205 if (@start_dt is null) 206 begin 207 /* 208 ** 18189, "Unknown starting time value %1!." 209 */ 210 raiserror 18189, @starttime 211 return (1) 212 end 213 if (@end_dt is null) 214 begin 215 /* 216 ** 18190, "Unknown ending time value %1!." 217 */ 218 raiserror 18190, @endtime 219 return (1) 220 end 221 222 /* Make sure the time params are in the right order. 223 ** The exception is when endtime is 00:00, which can 224 ** be taken to mean midnight. 225 */ 226 if ((datepart(hour, @end_dt) != 0) or 227 (datepart(minute, @end_dt) != 0) or 228 (datepart(second, @end_dt) != 0)) and 229 (@start_dt > @end_dt) 230 begin 231 /* 232 ** 18191, "Ending time must be later in the day than starting time." 233 */ 234 raiserror 18191 235 return (1) 236 end 237 238 exec @rtn_code = sp_gen_timerange_id @newmaxid output 239 if (@rtn_code != 0) 240 return (1) 241 242 243 244 /* Insert! */ 245 insert master.dbo.systimeranges values (@name, @newmaxid, @startdaynum, @enddaynum, @starttime, @endtime) 246 247 248 249 dbcc waketimerange 250 251 /* 252 ** 18192, "New time range created. ID number is %1!." 253 */ 254 exec sp_getmessage 18192, @msg output 255 print @msg, @newmaxid 256 return (0) 257 258 clean_all: 259 260 261 262 return (1) 263
exec sp_procxmode 'sp_add_time_range', 'AnyMode' go Grant Execute on sp_add_time_range to public go
| DEPENDENCIES |
| PROCS AND TABLES USED calls proc sybsystemprocs..sp_gen_timerange_id  reads table master..systimeranges (1)  calls proc sybsystemprocs..sp_ha_check_certified  reads table tempdb..sysobjects (1)  read_writes table master..systimeranges (1) calls proc sybsystemprocs..sp_getmessage reads table master..sysmessages (1) reads table master..syslanguages (1) reads table sybsystemprocs..sysusermessages calls proc sybsystemprocs..sp_validlang reads table master..syslanguages (1) |
