Back to OIM Explorer

dbo.QBM_ZCountryUTCOffset

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 2.948 characters

Interpretation

Database routine. Review parameters, called procedures, DBQueue inserts, and QBM_PJobCreate helper calls before assuming side effects.

Relations

No extracted relations.

Typed Edges

references source dbo.QBM_FGISessionContext source text reference
references source dbo.QBM_PSessionContextSet source text reference
references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

No direct source references extracted.

Complete Source

1CREATE PROCEDURE QBM_ZCountryUTCOffset(2  @SlotNumber int3)4AS5BEGIN6  DECLARE @GenProcID varchar(38)7  DECLARE @XUser nvarchar(64) = object_name(@@procid)8  DECLARE @Xdate datetime = getutcdate()9  DECLARE @uid_dialogCountry varchar(38)10  DECLARE @AVGUTCOffSet int11  DECLARE @IsDayLightSaving BIT12  DECLARE @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('')13  DECLARE @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext('XUser')14  DECLARE @ElementBuffer QBM_YCursorBuffer15  DECLARE @ElementCount int16  DECLARE @ElementIndex int17  BEGIN TRY18    INSERT INTO @ElementBuffer(UID1,19    UID2)20    SELECT21      c.uid_dialogCountry,22      p.GenProcID23    FROM dialogCountry c24    JOIN QBMDBQueueCurrent p25      WITH(readpast)26      ON c.uid_dialogCountry = p.uid_parameter27    WHERE28      SlotNumber = @SlotNumber29    SELECT @ElementCount = @@ROWCOUNT30    SELECT @ElementIndex = 131    WHILE @ElementIndex <= @ElementCount32    BEGIN33      SELECT34        TOP 1 @uid_dialogCountry = bu.UID1,35        @GenProcID = UID236      FROM @ElementBuffer bu37      WHERE38        bu.ElementIndex = @ElementIndex39      IF EXISTS(40        SELECT TOP 1 141        FROM dialogstate s42        JOIN dialogstateHasTimezone sht43          ON s.uid_dialogState = sht.uid_DialogState44        JOIN dialogTimezone tz45          ON sht.uid_dialogTimezone = tz.uid_dialogTimezone46        WHERE47          s.uid_dialogCountry = @uid_dialogCountry AND s.isinuse = 1 AND tz.utcoffset IS NOT NULL)48      BEGIN49        SELECT50          @AVGUTCOffSet = avg(tz.UTCOffset),51          @IsDayLightSaving = sign(max(convert(int, tz.IsDayLightSaving)))52        FROM dialogstate s53        JOIN DialogStateHasTimeZone sht54          ON s.uid_dialogstate = sht.uid_dialogState55        JOIN DialogTimeZone tz56          ON sht.uid_DialogTimeZone = tz.uid_dialogTimeZone57        WHERE58          s.uid_dialogCountry = @uid_dialogCountry AND s.isinuse = 1 AND tz.utcoffset IS NOT NULL59      END60      ELSE61      BEGIN62        IF EXISTS(63          SELECT TOP 1 164          FROM DialogCountryHasTimeZone cht65          JOIN dialogtimezone tz66            ON cht.uid_dialogTimezone = tz.uid_dialogTimezone67          WHERE68            uid_dialogCountry = @uid_dialogCountry AND tz.utcoffset IS NOT NULL)69        BEGIN70          SELECT71            @AVGUTCOffSet = avg(tz.UTCOffset),72            @IsDayLightSaving = sign(max(convert(int, tz.IsDayLightSaving)))73          FROM DialogCountryHasTimeZone cht74          JOIN DialogTimeZone tz75            ON cht.uid_DialogTimeZone = tz.uid_dialogTimeZone76          WHERE77            cht.uid_dialogCountry = @uid_dialogCountry AND tz.utcoffset IS NOT NULL78        END79        ELSE80        BEGIN81          SELECT @AVGUTCOffSet = 082          SELECT @IsDayLightSaving = 083        END84      END85      EXEC QBM_PSessionContextSet 'GenProcID',86        @GenProcID87      EXEC QBM_PSessionContextSet 'XUser',88        @XUser89      UPDATE dialogCountry90      SET AVGUTCOffSet = @AVGUTCOffSet,91      IsDayLightSaving = @IsDayLightSaving,92      XDateUpdated = @Xdate,93      XUserUpdated = @XUser94      WHERE95        uid_DialogCountry = @uid_DialogCountry AND(isnull(AVGUTCOffSet, 0) <> @AVGUTCOffSet OR AVGUTCOffSet IS NULL OR isnull(IsDayLightSaving,96      0) <> @IsDayLightSaving OR IsDayLightSaving IS NULL)97      SELECT @ElementIndex += 198    END99  END TRY100  BEGIN CATCH101    EXEC QBM_PSessionErrorAdd DEFAULT102    RAISERROR('',103    18,104    1)105      WITH NOWAIT106  END CATCH107  ende:108  EXEC QBM_PSessionContextSet 'GenProcID',109    @GenProcID_R110  EXEC QBM_PSessionContextSet 'XUser',111    @XUser_R112  RETURN113END

Open raw exported source

1    create   procedure QBM_ZCountryUTCOffset (@SlotNumber int) as begin declare @GenProcID varchar(38) declare @XUser nvarchar(64) = object_name2(@@procid) declare @Xdate datetime = getutcdate() declare @uid_dialogCountry varchar(38) declare @AVGUTCOffSet int declare @IsDayLightSaving bit declare3 @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('') declare @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext('XUser') declare @ElementBuffer QBM_YCursorBuffer4 declare @ElementCount int declare @ElementIndex int BEGIN TRY insert into @ElementBuffer (UID1, UID2) select c.uid_dialogCountry, p.GenProcID from dialogCountry5 c join QBMDBQueueCurrent p with (readpast) on c.uid_dialogCountry = p.uid_parameter where SlotNumber = @SlotNumber select @ElementCount = @@ROWCOUNT select6 @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top 1 @uid_dialogCountry = bu.UID1 , @GenProcID = UID2 from @ElementBuffer bu where7 bu.ElementIndex = @ElementIndex  if exists (select top 1 1 from dialogstate s join dialogstateHasTimezone sht on s.uid_dialogState = sht.uid_DialogState8 join dialogTimezone tz on sht.uid_dialogTimezone = tz.uid_dialogTimezone where s.uid_dialogCountry = @uid_dialogCountry and s.isinuse = 1 and tz.utcoffset9 is not null ) begin  select @AVGUTCOffSet = avg(tz.UTCOffset), @IsDayLightSaving = sign(max(convert(int, tz.IsDayLightSaving))) from dialogstate s join10 DialogStateHasTimeZone sht on s.uid_dialogstate = sht.uid_dialogState join DialogTimeZone tz on sht.uid_DialogTimeZone = tz.uid_dialogTimeZone where s.uid_dialogCountry11 = @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 dialogtimezone12 tz on cht.uid_dialogTimezone = tz.uid_dialogTimezone where uid_dialogCountry = @uid_dialogCountry and tz.utcoffset is not null ) begin  select @AVGUTCOffSet13 = avg(tz.UTCOffset), @IsDayLightSaving = sign(max(convert(int, tz.IsDayLightSaving))) from DialogCountryHasTimeZone cht join DialogTimeZone tz on cht.uid_DialogTimeZone14 = 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_DialogCountry17 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_PSessionContextSet19 'GenProcID', @GenProcID_R exec QBM_PSessionContextSet 'XUser', @XUser_R return end 20