Back to OIM Explorer

dbo.QBM_ZStateUTCOffset

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure. bulk DBQueue insert -> QBM-K-CommonStateUTCOffset2 / QBM_ZStateUTCOffset2 at line 8; References QBM_PDBQueueInsert_Bulk

Source: sandbox-db sys.sql_modules

Source size: 2.263 characters

Interpretation

  • Database routine. Review parameters, called procedures, DBQueue inserts, and QBM_PJobCreate helper calls before assuming side effects.
  • DBQueue relation detected. Follow the task procedure and referenced-by list for async processing.

Relations

  • bulk DBQueue insert -> QBM-K-CommonStateUTCOffset2 / QBM_ZStateUTCOffset2 at line 8
  • References QBM_PDBQueueInsert_Bulk

Typed Edges

  • queues DBQueue task QBM_ZStateUTCOffset2 at line 8 bulk DBQueue insert -> QBM-K-CommonStateUTCOffset2 / QBM_ZStateUTCOffset2 at line 8
  • references source dbo.QBM_PDBQueueInsert_Bulk source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • queues DBQueue task QBM-K-CommonStateUTCOffset2 -> QBM_ZStateUTCOffset2 QBM_PDBQueueInsert_bulk 'QBM-K-CommonStateUTCOffset2', @DBQueueElements_01 delete @DBQueueCurrent from @DBQueueCurrent cu join @DBQueueElements_01 el on cu.UID_Parameter = el.Object update DialogState set AVGUTCOffSet = x.AVGUTCOffSet , Is…

References

Referenced By

Complete Source

SQL97 lines
1CREATE PROCEDURE QBM_ZStateUTCOffset(2  @SlotNumber int3)4AS5BEGIN6  DECLARE @uid_dialogState varchar(38)7  DECLARE @AVGUTCOffSet int8  DECLARE @IsDayLightSaving BIT9  DECLARE @GenProcID varchar(38)10  DECLARE @DBQueueCurrent QBM_YDBQueueCurrent11  DECLARE @XUser nvarchar(64) = object_name(@@procid)12  DECLARE @Xdate datetime = getutcdate()13  BEGIN TRY14    INSERT INTO @DBQueueCurrent(UID_DialogDBQueue,15    UID_Parameter,16    UID_SubParameter,17    GenProcID)18    SELECT19      UID_DialogDBQueue,20      UID_Parameter,21      UID_SubParameter,22      GenProcID23    FROM QBMDBQueueCurrent cu24      WITH(readpast)25    WHERE26      cu.SlotNumber = @SlotNumber27    IF @@rowcount = 028    BEGIN29      GOTO EndLabel30    END31    SELECT TOP 1 @GenProcID = GenProcID32    FROM @DBQueueCurrent p33    DECLARE @DBQueueElements_01 QBM_YDBQueueRaw34    INSERT INTO @DBQueueElements_01(Object,35    SubObject,36    GenProcID)37    SELECT38      x.uid,39      NULL,40      @GenProcID41    FROM(42    SELECT43      DISTINCT s.UID_DialogState AS uid44    FROM DialogState s45    JOIN @DBQueueCurrent cu46      ON cu.UID_Parameter = s.UID_DialogState47    LEFT48    OUTER49    JOIN DialogStateHasTimeZone sht50      ON s.UID_DialogState = sht.UID_DialogState51    LEFT52    OUTER53    JOIN DialogTimeZone tz54      ON sht.UID_DialogTimeZone = tz.UID_DialogTimeZone AND tz.UTCOffset IS NOT NULL55    WHERE56      tz.UID_DialogTimeZone IS NULL) AS x57    EXEC QBM_PDBQueueInsert_bulk 'QBM-K-CommonStateUTCOffset2',58      @DBQueueElements_0159    DELETE @DBQueueCurrent60    FROM @DBQueueCurrent cu61    JOIN @DBQueueElements_01 el62      ON cu.UID_Parameter = el.Object63    UPDATE DialogState64    SET AVGUTCOffSet = x.AVGUTCOffSet,65    IsDayLightSaving = x.IsDayLightSaving,66    XDateUpdated = @Xdate,67    XUserUpdated = @XUser68    FROM DialogState su69    JOIN(70    SELECT71      s.UID_DialogState,72      avg(tz.UTCOffset) AS AVGUTCOffset,73      sign(max(convert(int, tz.IsDayLightSaving))) AS IsDayLightSaving74    FROM @DBQueueCurrent cu75    JOIN DialogState s76      ON cu.UID_Parameter = s.UID_DialogState77    JOIN DialogStateHasTimeZone sht78      ON s.UID_DialogState = sht.UID_DialogState79    JOIN DialogTimeZone tz80      ON sht.UID_DialogTimeZone = tz.UID_DialogTimeZone AND tz.UTCOffset IS NOT NULL81    GROUP BY s.UID_DialogState) AS x82      ON su.UID_DialogState = x.UID_DialogState83    WHERE84      isnull(su.AVGUTCOffset,85    0) <> x.AVGUTCOffSet OR isnull(su.IsDayLightSaving,86    0) <> x.IsDayLightSaving87  END TRY88  BEGIN CATCH89    EXEC QBM_PSessionErrorAdd DEFAULT90    RAISERROR('',91    18,92    1)93      WITH NOWAIT94  END CATCH95  endLabel:96  RETURN97END
Open raw exported source
SQL · Raw16 lines
1    create   procedure QBM_ZStateUTCOffset (@SlotNumber int) as begin  declare @uid_dialogState varchar(38) declare @AVGUTCOffSet int declare @IsDayLightSaving2 bit declare @GenProcID varchar(38) declare @DBQueueCurrent QBM_YDBQueueCurrent declare @XUser nvarchar(64) = object_name(@@procid) declare @Xdate datetime3 = getutcdate() BEGIN TRY insert into @DBQueueCurrent(UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID) select UID_DialogDBQueue, UID_Parameter4, UID_SubParameter, GenProcID from QBMDBQueueCurrent cu with (readpast) where cu.SlotNumber = @SlotNumber if @@rowcount = 0 begin goto EndLabel end select5 top 1 @GenProcID = GenProcID from @DBQueueCurrent p declare @DBQueueElements_01 QBM_YDBQueueRaw insert into @DBQueueElements_01 (Object, SubObject, GenProcID6) select x.uid, null, @GenProcID from (select distinct s.UID_DialogState as uid from DialogState s join @DBQueueCurrent cu on cu.UID_Parameter = s.UID_DialogState7 left outer join DialogStateHasTimeZone sht on s.UID_DialogState = sht.UID_DialogState left outer join DialogTimeZone tz on sht.UID_DialogTimeZone = tz.UID_DialogTimeZone8 and tz.UTCOffset is not null where tz.UID_DialogTimeZone is null ) as x exec QBM_PDBQueueInsert_bulk 'QBM-K-CommonStateUTCOffset2', @DBQueueElements_019  delete  @DBQueueCurrent from @DBQueueCurrent cu join @DBQueueElements_01 el on cu.UID_Parameter = el.Object  update DialogState set AVGUTCOffSet = x.AVGUTCOffSet10 , IsDayLightSaving = x.IsDayLightSaving , XDateUpdated = @Xdate , XUserUpdated = @XUser from DialogState su join ( select s.UID_DialogState, avg(tz.UTCOffset11) as AVGUTCOffset, sign(max(convert(int, tz.IsDayLightSaving))) as IsDayLightSaving from @DBQueueCurrent cu join DialogState s on cu.UID_Parameter = s.UID_DialogState12 join DialogStateHasTimeZone sht on s.UID_DialogState = sht.UID_DialogState join DialogTimeZone tz on sht.UID_DialogTimeZone = tz.UID_DialogTimeZone and13 tz.UTCOffset is not null group by s.UID_DialogState ) as x on su.UID_DialogState = x.UID_DialogState where isnull(su.AVGUTCOffset, 0) <> x.AVGUTCOffSet14 or isnull(su.IsDayLightSaving, 0) <> x.IsDayLightSaving END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH15 endLabel: return end 16