Back to OIM Explorer

dbo.QER_ZQERWorkingMethodCleanUp

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 2.509 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_FCVIntToString source text reference
references source dbo.QBM_FGIModuleExists source text reference
references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

No direct source references extracted.

Complete Source

1CREATE PROCEDURE QER_ZQERWorkingMethodCleanUp(2  @SlotnumberDummy int = 0,3  @Dummy1 varchar(38) = NULL,4  @Dummy2 varchar(38) = NULL,5  @Dummy3 varchar(38) = NULL6)7AS8BEGIN9  DECLARE @SQLCMD nvarchar(max)10  DECLARE @usedMethods QBM_YParameterList11  DECLARE @UnUsedMethods QBM_YParameterList12  DECLARE @DebugSwitch int = 013  BEGIN TRY14    INSERT INTO @usedMethods(Parameter1,15    Parameter2)16    SELECT17      m.UID_QERWorkingMethod,18      dbo.QBM_FCVIntToString(m.RevisionNumber)19    FROM PersonWantsOrg pwo20    JOIN QERWorkingMethod m21      ON pwo.UID_QERWorkingMethod = m.UID_QERWorkingMethod22    UNION23    SELECT24      DISTINCT m.UID_QERWorkingMethod,25      dbo.QBM_FCVIntToString(m.RevisionNumber)26    FROM PWOHelperPWO h27    JOIN QERWorkingStep s28      ON s.UID_QERWorkingStep = h.UID_QERWorkingStep29    JOIN QERWorkingMethod m30      ON s.UID_QERWorkingMethod = m.UID_QERWorkingMethod31    IF dbo.QBM_FGIModuleExists('ATT') = 132    BEGIN33      SELECT34        @SQLCMD = '35	select m.UID_QERWorkingMethod, dbo.QBM_FCVIntToString( m.RevisionNumber)36	from AttestationCase pwo join QERWorkingMethod m on pwo.UID_QERWorkingMethod = m.UID_QERWorkingMethod37	union38	select m.UID_QERWorkingMethod, dbo.QBM_FCVIntToString( m.RevisionNumber)39	from AttestationHelper h join QERWorkingStep s on s.UID_QERWorkingStep = h.UID_QERWorkingStep40							join QERWorkingMethod m on s.UID_QERWorkingMethod = m.UID_QERWorkingMethod41	'42      INSERT INTO @usedMethods(Parameter1,43      Parameter2)44      EXEC sp_executesql @SQLCmd45    END46    INSERT INTO @UnUsedMethods(Parameter1,47    Parameter2)48    SELECT49      m.UID_QERWorkingMethod,50      dbo.QBM_FCVIntToString(m.RevisionNumber)51    FROM QERWorkingMethod m52    LEFT53    OUTER54    JOIN @usedMethods u55      ON m.UID_QERWorkingMethod = u.Parameter156    WHERE57      u.Parameter1 IS NULL58    IF @DebugSwitch > 059    BEGIN60      print 'Used Methods'61      SELECT *62      FROM @usedMethods print 'UnUsed Methods'63      SELECT *64      FROM @UnUsedMethods65    END66    IF @DebugSwitch > 067    BEGIN68      print 'Löschkandidaten'69      SELECT70        m.UID_QERWorkingMethod,71        m.RevisionNumber,72        s.RevisionNumber73      FROM QERWorkingMethod m74      JOIN @UnUsedMethods u75        ON u.Parameter1 = m.UID_QERWorkingMethod76      JOIN PWODecisionSubMethod s77        ON m.UID_PWODecisionSubMethod = s.UID_PWODecisionSubMethod78      WHERE79        m.RevisionNumber < s.RevisionNumber80    END81    DELETE QERWorkingMethod82    FROM QERWorkingMethod m83    JOIN @UnUsedMethods u84      ON u.Parameter1 = m.UID_QERWorkingMethod85    LEFT86    OUTER87    JOIN PWODecisionSubMethod s88      ON m.UID_PWODecisionSubMethod = s.UID_PWODecisionSubMethod89    WHERE90      m.RevisionNumber < isnull(s.RevisionNumber,91    1073741824)92  END TRY93  BEGIN CATCH94    EXEC QBM_PSessionErrorAdd DEFAULT95    RAISERROR('',96    18,97    1)98      WITH NOWAIT99  END CATCH100END

Open raw exported source

1    create   procedure QER_ZQERWorkingMethodCleanUp ( @SlotnumberDummy int = 0 , @Dummy1 varchar(38) = null , @Dummy2 varchar(38) = null , @Dummy32 varchar(38) = null ) AS begin  declare @SQLCMD nvarchar(max) declare @usedMethods QBM_YParameterList declare @UnUsedMethods QBM_YParameterList declare3 @DebugSwitch int = 0 BEGIN TRY insert into @usedMethods(Parameter1, Parameter2) select m.UID_QERWorkingMethod, dbo.QBM_FCVIntToString( m.RevisionNumber4) from PersonWantsOrg pwo join QERWorkingMethod m on pwo.UID_QERWorkingMethod = m.UID_QERWorkingMethod union select distinct m.UID_QERWorkingMethod, dbo.QBM_FCVIntToString5( m.RevisionNumber) from PWOHelperPWO h join QERWorkingStep s on s.UID_QERWorkingStep = h.UID_QERWorkingStep join QERWorkingMethod m on s.UID_QERWorkingMethod6 = m.UID_QERWorkingMethod if dbo.QBM_FGIModuleExists('ATT') = 1 begin select @SQLCMD = '7	select m.UID_QERWorkingMethod, dbo.QBM_FCVIntToString( m.RevisionNumber)8	from AttestationCase pwo join QERWorkingMethod m on pwo.UID_QERWorkingMethod = m.UID_QERWorkingMethod9	union10	select m.UID_QERWorkingMethod, dbo.QBM_FCVIntToString( m.RevisionNumber)11	from AttestationHelper h join QERWorkingStep s on s.UID_QERWorkingStep = h.UID_QERWorkingStep12							join QERWorkingMethod m on s.UID_QERWorkingMethod = m.UID_QERWorkingMethod13	'14 insert into @usedMethods(Parameter1, Parameter2) exec sp_executesql @SQLCmd end insert into @UnUsedMethods(Parameter1, Parameter2) select m.UID_QERWorkingMethod15, dbo.QBM_FCVIntToString( m.RevisionNumber) from QERWorkingMethod m left outer join @usedMethods u on m.UID_QERWorkingMethod = u.Parameter1 where u.Parameter116 is null if @DebugSwitch > 0 begin print 'Used Methods' select * from @usedMethods print 'UnUsed Methods' select * from @UnUsedMethods end if @DebugSwitch17 > 0 begin print 'Löschkandidaten' select m.UID_QERWorkingMethod, m.RevisionNumber, s.RevisionNumber from QERWorkingMethod m join @UnUsedMethods u on u.Parameter118 = m.UID_QERWorkingMethod join PWODecisionSubMethod s on m.UID_PWODecisionSubMethod = s.UID_PWODecisionSubMethod  where m.RevisionNumber < s.RevisionNumber19 end delete QERWorkingMethod from QERWorkingMethod m join @UnUsedMethods u on u.Parameter1 = m.UID_QERWorkingMethod left outer  join PWODecisionSubMethod20 s on m.UID_PWODecisionSubMethod = s.UID_PWODecisionSubMethod  where m.RevisionNumber < isnull(s.RevisionNumber, 1073741824) END TRY BEGIN CATCH exec QBM_PSessionErrorAdd21 default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH end 22