Back to OIM Explorer

dbo.QBM_PModuleDependCollFill

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 3.481 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_FGISessionErrorRethrow source text reference
references source dbo.QBM_PSessionErrorAdd source text reference
references source dbo.QBM_PTransportHistoryInsert source text reference

References

Referenced By

No direct source references extracted.

Complete Source

1CREATE PROCEDURE QBM_PModuleDependCollFill2AS3BEGIN4  DECLARE @Xdate datetime = getutcdate()5  DECLARE @XUser nvarchar(64)6  DECLARE @GenProcID varchar(38)7  SET XACT_ABORT OFF8  BEGIN TRY9    SELECT @GenProcID = dbo.QBM_FGISessionContext('')10    SELECT @XUser = dbo.QBM_FGISessionContext('XUser')11    BEGIN12      TRANSACTION13      IF NOT EXISTS(14        SELECT TOP 1 115        FROM QBMModuleDef16        WHERE17          UID_ModuleDef = 'CCC-Moduledefinition')18      BEGIN19        INSERT INTO QBMModuleDef(UID_ModuleDef,20        DisplayValue,21        XObjectKey,22        Modulename,23        ModuleVersion,24        MigrationVersion,25        XDateInserted,26        XDateUpdated,27        XUserInserted,28        XUserUpdated)29        SELECT30          'CCC-Moduledefinition',31          'Customer configured content',32          '<Key><T>QBMModuleDef</T><P>CCC-Moduledefinition</P></Key>',33          'CCC',34          d.ModuleVersion,35          d.MigrationVersion,36          @Xdate,37          @Xdate,38          @XUser,39          @XUser40        FROM QBMModuleDef d41        WHERE42          d.ModuleName = 'QBM'43        EXEC QBM_PTransportHistoryInsert 'CCC',44          @XUser45      END46      ELSE47      BEGIN48        UPDATE QBMModuleDef49        SET ModuleVersion = x.ModuleVersion,50        MigrationVersion = x.MigrationVersion,51        XDateUpdated = @Xdate,52        XUserUpdated = @XUser53        FROM QBMModuleDef d,54        (55        SELECT56          a.MigrationVersion,57          a.ModuleVersion58        FROM QBMModuleDef a59        WHERE60          a.ModuleName = 'QBM') AS x61        WHERE62          d.ModuleName = 'CCC' AND(isnull(d.ModuleVersion, '') <> x.ModuleVersion OR isnull(d.MigrationVersion,63        '') <> x.MigrationVersion)64      END65      UPDATE QBMModuleDef66      SET ModuleVersion = x.ModuleVersion,67      XDateUpdated = @Xdate,68      XUserUpdated = @XUser69      FROM QBMModuleDef d,70      (71      SELECT72        a.MigrationVersion,73        a.ModuleVersion74      FROM QBMModuleDef a75      WHERE76        a.ModuleName = 'QBM') AS x77      WHERE78        d.ModuleName = 'MDK' AND(isnull(d.ModuleVersion, '') <> x.ModuleVersion)79      DELETE QBMModuleDependCollection80      INSERT INTO QBMModuleDependCollection(UID_ModulePredecessor,81      UID_ModuleFollower)82      SELECT83        UID_ModuleDef,84        UID_ModuleDef85      FROM QBMModuleDef86      UNION87      SELECT88        UID_ModuleDef,89        'CCC-Moduledefinition'90      FROM QBMModuleDef91      UNION92      SELECT93        d.UID_ModulePredecessor,94        d.UID_ModuleFollower95      FROM QBMModuledepend d96      DECLARE @lauf int = 197      WHILE @lauf > 098      BEGIN99        INSERT INTO QBMModuleDependCollection(UID_ModulePredecessor,100        UID_ModuleFollower)101        SELECT102          DISTINCT v.UID_ModulePredecessor,103          n.UID_ModuleFollower104        FROM QBMModuleDependCollection v105        JOIN QBMModuleDependCollection n106          ON v.UID_ModuleFollower = n.UID_ModulePredecessor107        WHERE108          NOT EXISTS(109        SELECT TOP 1 1110        FROM QBMModuleDependCollection co111        WHERE112          co.UID_ModulePredecessor = v.UID_ModulePredecessor AND co.UID_ModuleFollower = n.UID_ModuleFollower)113        SELECT @lauf = @@ROWCOUNT114      END115      COMMIT TRANSACTION;116        WITH hierarchy(UID_ModuleDef,117      UID_ModuleFollower,118      SortOrder) AS((119      SELECT120        d.UID_ModulePredecessor, d.UID_ModuleFollower, 1 AS SortOrder121      FROM QBMModuleDepend d122      UNION all123      SELECT124        de.UID_ModuleDef, 'CCC-Moduledefinition', 1125      FROM QBMModuleDef de126      UNION all127      SELECT128        d.UID_ModulePredecessor, d.UID_ModulePredecessor, 0 AS SortOrder129      FROM QBMModuleDepend d)130      UNION all131      SELECT132        a.UID_ModulePredecessor,133        h.UID_ModuleFollower,134        h.SortOrder +1135      FROM QBMModuleDepend a136      JOIN hierarchy h137        ON a.UID_ModuleFollower = h.UID_ModuleDef)138      UPDATE QBMModuleDef139      SET SortOrder = x.SortOrder,140      XDateUpdated = @Xdate,141      XUserUpdated = @XUser142      FROM QBMModuleDef md143      JOIN(144      SELECT145        UID_ModuleFollower AS UID_ModuleDef,146        max(SortOrder) AS SortOrder147      FROM hierarchy148      GROUP BY UID_ModuleFollower) AS x149        ON x.UID_ModuleDef = md.UID_ModuleDef150    END TRY151    BEGIN CATCH152      EXEC QBM_PSessionErrorAdd DEFAULT153      DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()154      RAISERROR(@Rethrow,155      18,156      1)157        WITH NOWAIT158    END CATCH159    ende:160  END

Open raw exported source

1         create   procedure QBM_PModuleDependCollFill as begin declare @Xdate datetime = getutcdate() declare @XUser nvarchar(64) declare @GenProcID2 varchar(38) SET XACT_ABORT OFF BEGIN TRY select @GenProcID = dbo.QBM_FGISessionContext('') select @XUser = dbo.QBM_FGISessionContext('XUser') begin transaction3  if not exists (select top 1 1 from QBMModuleDef where UID_ModuleDef = 'CCC-Moduledefinition' ) begin insert into QBMModuleDef (UID_ModuleDef, DisplayValue4, XObjectKey , Modulename, ModuleVersion, MigrationVersion , XDateInserted, XDateUpdated, XUserInserted, XUserUpdated) select 'CCC-Moduledefinition' , 5'Customer configured content', '<Key><T>QBMModuleDef</T><P>CCC-Moduledefinition</P></Key>' , 'CCC', d.ModuleVersion, d.MigrationVersion , @Xdate, @Xdate6, @XUser, @XUser from QBMModuleDef d where d.ModuleName = 'QBM' exec QBM_PTransportHistoryInsert 'CCC', @XUser end else begin update QBMModuleDef set ModuleVersion7 = x.ModuleVersion , MigrationVersion = x.MigrationVersion , XDateUpdated = @Xdate , XUserUpdated = @XUser from QBMModuleDef d , ( select a.MigrationVersion8, a.ModuleVersion from QBMModuleDef a where a.ModuleName = 'QBM' ) as x where d.ModuleName = 'CCC' and ( isnull(d.ModuleVersion, '') <> x.ModuleVersion9 or isnull(d.MigrationVersion, '') <> x.MigrationVersion ) end  update QBMModuleDef set ModuleVersion = x.ModuleVersion , XDateUpdated = @Xdate , XUserUpdated10 = @XUser from QBMModuleDef d , ( select a.MigrationVersion, a.ModuleVersion from QBMModuleDef a where a.ModuleName = 'QBM' ) as x where d.ModuleName =11 'MDK' and ( isnull(d.ModuleVersion, '') <> x.ModuleVersion ) delete QBMModuleDependCollection insert into QBMModuleDependCollection(UID_ModulePredecessor12, UID_ModuleFollower) select UID_ModuleDef, UID_ModuleDef from QBMModuleDef union select UID_ModuleDef, 'CCC-Moduledefinition' from QBMModuleDef union 13select d.UID_ModulePredecessor, d.UID_ModuleFollower from QBMModuledepend d declare @lauf int = 1 while @lauf > 0 begin insert into QBMModuleDependCollection14(UID_ModulePredecessor, UID_ModuleFollower) select distinct v.UID_ModulePredecessor, n.UID_ModuleFollower from QBMModuleDependCollection v join QBMModuleDependCollection15 n on v.UID_ModuleFollower = n.UID_ModulePredecessor where Not exists (select top 1 1 from QBMModuleDependCollection co where co.UID_ModulePredecessor 16= v.UID_ModulePredecessor and co.UID_ModuleFollower = n.UID_ModuleFollower ) select @lauf = @@ROWCOUNT end commit transaction  ; with hierarchy (UID_ModuleDef17, UID_ModuleFollower, SortOrder) as ( (select d.UID_ModulePredecessor, d.UID_ModuleFollower, 1 as SortOrder from QBMModuleDepend d union all select de.UID_ModuleDef18, 'CCC-Moduledefinition', 1 from QBMModuleDef de union all  select d.UID_ModulePredecessor, d.UID_ModulePredecessor, 0 as SortOrder from QBMModuleDepend19 d ) union all select a.UID_ModulePredecessor, h.UID_ModuleFollower, h.SortOrder +1 from QBMModuleDepend a join hierarchy h on a.UID_ModuleFollower = h.UID_ModuleDef20 ) update QBMModuleDef set SortOrder = x.SortOrder , XDateUpdated = @Xdate , XUserUpdated = @XUser from QBMModuleDef md join ( select UID_ModuleFollower21 as UID_ModuleDef, max(SortOrder) as SortOrder from hierarchy group by UID_ModuleFollower ) as x on x.UID_ModuleDef = md.UID_ModuleDef  END TRY BEGIN CATCH22 exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH 23ende: end 24