dbo.QBM_ZCountryUTCOffset

1    create   procedure QBM_ZCountryUTCOffset (@SlotNumber int) as begin declare @GenProcID varchar(38) declare @XUser nvarchar(64) = object_name
2(@@procid) declare @Xdate datetime = getutcdate() declare @uid_dialogCountry varchar(38) declare @AVGUTCOffSet int declare @IsDayLightSaving bit declare
3 @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('') declare @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext('XUser') declare @ElementBuffer QBM_YCursorBuffer
4 declare @ElementCount int declare @ElementIndex int BEGIN TRY insert into @ElementBuffer (UID1, UID2) select c.uid_dialogCountry, p.GenProcID from dialogCountry
5 c join QBMDBQueueCurrent p with (readpast) on c.uid_dialogCountry = p.uid_parameter where SlotNumber = @SlotNumber select @ElementCount = @@ROWCOUNT select
6 @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top 1 @uid_dialogCountry = bu.UID1 , @GenProcID = UID2 from @ElementBuffer bu where
7 bu.ElementIndex = @ElementIndex  if exists (select top 1 1 from dialogstate s join dialogstateHasTimezone sht on s.uid_dialogState = sht.uid_DialogState
8 join dialogTimezone tz on sht.uid_dialogTimezone = tz.uid_dialogTimezone where s.uid_dialogCountry = @uid_dialogCountry and s.isinuse = 1 and tz.utcoffset
9 is not null ) begin  select @AVGUTCOffSet = avg(tz.UTCOffset), @IsDayLightSaving = sign(max(convert(int, tz.IsDayLightSaving))) from dialogstate s join
10 DialogStateHasTimeZone sht on s.uid_dialogstate = sht.uid_dialogState join DialogTimeZone tz on sht.uid_DialogTimeZone = tz.uid_dialogTimeZone where s.uid_dialogCountry
11 = @uid_dialogCountry and s.isinuse = 1 and tz.utcoffset is not null end else begin  if exists (select top 1 1 from DialogCountryHasTimeZone cht join dialogtimezone
12 tz on cht.uid_dialogTimezone = tz.uid_dialogTimezone where uid_dialogCountry = @uid_dialogCountry and tz.utcoffset is not null ) begin  select @AVGUTCOffSet
13 = avg(tz.UTCOffset), @IsDayLightSaving = sign(max(convert(int, tz.IsDayLightSaving))) from DialogCountryHasTimeZone cht join DialogTimeZone tz on cht.uid_DialogTimeZone
14 = tz.uid_dialogTimeZone where cht.uid_dialogCountry = @uid_dialogCountry and tz.utcoffset is not null end else begin  select @AVGUTCOffSet = 0 select 
15@IsDayLightSaving = 0 end end exec QBM_PSessionContextSet 'GenProcID', @GenProcID exec QBM_PSessionContextSet 'XUser', @XUser update dialogCountry set 
16AVGUTCOffSet = @AVGUTCOffSet , IsDayLightSaving = @IsDayLightSaving , XDateUpdated = @Xdate , XUserUpdated = @XUser where uid_DialogCountry = @uid_DialogCountry
17 and (isnull(AVGUTCOffSet, 0) <> @AVGUTCOffSet or AVGUTCOffSet is null or isnull(IsDayLightSaving, 0) <> @IsDayLightSaving or IsDayLightSaving is null 
18) select @ElementIndex += 1 end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH ende: exec QBM_PSessionContextSet
19 'GenProcID', @GenProcID_R exec QBM_PSessionContextSet 'XUser', @XUser_R return end 
20