dbo.QER_ZQERWorkingMethodCleanUp

1    create   procedure QER_ZQERWorkingMethodCleanUp ( @SlotnumberDummy int = 0 , @Dummy1 varchar(38) = null , @Dummy2 varchar(38) = null , @Dummy3
2 varchar(38) = null ) AS begin  declare @SQLCMD nvarchar(max) declare @usedMethods QBM_YParameterList declare @UnUsedMethods QBM_YParameterList declare
3 @DebugSwitch int = 0 BEGIN TRY insert into @usedMethods(Parameter1, Parameter2) select m.UID_QERWorkingMethod, dbo.QBM_FCVIntToString( m.RevisionNumber
4) from PersonWantsOrg pwo join QERWorkingMethod m on pwo.UID_QERWorkingMethod = m.UID_QERWorkingMethod union select distinct m.UID_QERWorkingMethod, dbo.QBM_FCVIntToString
5( m.RevisionNumber) from PWOHelperPWO h join QERWorkingStep s on s.UID_QERWorkingStep = h.UID_QERWorkingStep join QERWorkingMethod m on s.UID_QERWorkingMethod
6 = 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_QERWorkingMethod
9	union
10	select m.UID_QERWorkingMethod, dbo.QBM_FCVIntToString( m.RevisionNumber)
11	from AttestationHelper h join QERWorkingStep s on s.UID_QERWorkingStep = h.UID_QERWorkingStep
12							join QERWorkingMethod m on s.UID_QERWorkingMethod = m.UID_QERWorkingMethod
13	'
14 insert into @usedMethods(Parameter1, Parameter2) exec sp_executesql @SQLCmd end insert into @UnUsedMethods(Parameter1, Parameter2) select m.UID_QERWorkingMethod
15, dbo.QBM_FCVIntToString( m.RevisionNumber) from QERWorkingMethod m left outer join @usedMethods u on m.UID_QERWorkingMethod = u.Parameter1 where u.Parameter1
16 is null if @DebugSwitch > 0 begin print 'Used Methods' select * from @usedMethods print 'UnUsed Methods' select * from @UnUsedMethods end if @DebugSwitch
17 > 0 begin print 'Löschkandidaten' select m.UID_QERWorkingMethod, m.RevisionNumber, s.RevisionNumber from QERWorkingMethod m join @UnUsedMethods u on u.Parameter1
18 = m.UID_QERWorkingMethod join PWODecisionSubMethod s on m.UID_PWODecisionSubMethod = s.UID_PWODecisionSubMethod  where m.RevisionNumber < s.RevisionNumber
19 end delete QERWorkingMethod from QERWorkingMethod m join @UnUsedMethods u on u.Parameter1 = m.UID_QERWorkingMethod left outer  join PWODecisionSubMethod
20 s on m.UID_PWODecisionSubMethod = s.UID_PWODecisionSubMethod  where m.RevisionNumber < isnull(s.RevisionNumber, 1073741824) END TRY BEGIN CATCH exec QBM_PSessionErrorAdd
21 default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH end 
22