Back to OIM Explorer

dbo.QBM_PMultilanguageDoubleClear

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 6.507 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_FCVDatetimeToString source text reference
references source dbo.QBM_FCVGUIDToModuleOwner source text reference
references source dbo.QBM_FCVIntToString source text reference
references source dbo.QBM_FCVStringPadLeft source text reference
references source dbo.QBM_FGIDBOwner source text reference
references source dbo.QBM_FGISessionErrorRethrow source text reference
references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

No direct source references extracted.

Complete Source

1CREATE PROCEDURE QBM_PMultilanguageDoubleClear2AS3BEGIN4  DECLARE @ich varchar(3)5  DECLARE @DebugSwitch int = 06  DECLARE @CountRows int7  DECLARE @DieDoppelten1 QBM_YMultilanguageDouble8  DECLARE @DieDoppelten2 QBM_YMultilanguageDouble9  DECLARE @DieDoppelten3 QBM_YMultilanguageDouble10  DECLARE @MDKMLGuidReplace TABLE(UID_DialogMultiLanguageNew varchar(38) collate database_default,11  UID_DialogMultiLanguageOld varchar(38) collate database_default,12  XUserInserted nvarchar(64) collate database_default,13  XUserUpdated nvarchar(64) collate database_default,14  XDateInserted datetime,15  XDateUpdated datetime)16  SET XACT_ABORT OFF17  BEGIN TRY18    SET nocount19      ON20    SELECT @ich = dbo.QBM_FGIDBOwner()21    INSERT INTO @DieDoppelten1(UID_DialogColumn,22    UID_DialogCulture,23    EntryKey)24    SELECT25      m.UID_DialogColumn,26      m.UID_DialogCulture,27      m.EntryKey28    FROM DialogMultiLanguage m29    GROUP BY m.UID_DialogColumn,30    m.UID_DialogCulture,31    m.EntryKey32    HAVING COUNT(*) > 133    IF @@ROWCOUNT = 034    BEGIN35      GOTO endlabel36    END37    INSERT INTO @MDKMLGuidReplace(UID_DialogMultiLanguageNew,38    UID_DialogMultiLanguageOld,39    XDateInserted,40    XDateUpdated,41    XUserInserted,42    XUserUpdated)43    SELECT44      ml.UID_DialogMultiLanguage,45      mh.UID_DialogMultiLanguage,46      mh.XDateInserted,47      mh.XDateUpdated,48      mh.XUserInserted,49      mh.XUserUpdated50    FROM DialogMultiLanguage ml51    JOIN DialogMultiLanguage mh52      ON ml.UID_DialogColumn = mh.UID_DialogColumn AND ml.UID_DialogCulture = mh.UID_DialogCulture AND ml.EntryKey = mh.EntryKey53    JOIN DialogColumn c54      WITH(readpast)55      ON ml.UID_DialogColumn = c.UID_DialogColumn56    JOIN DialogTable t57      WITH(readpast)58      ON c.UID_DialogTable = t.UID_DialogTable59    JOIN QBMModuleDef mol60      WITH(readpast)61      ON dbo.QBM_FCVGUIDToModuleOwner(ml.UID_DialogMultiLanguage) = mol.ModuleName62    JOIN QBMModuleDef moh63      WITH(readpast)64      ON dbo.QBM_FCVGUIDToModuleOwner(mh.UID_DialogMultiLanguage) = moh.ModuleName65    WHERE66      ml.UID_DialogMultiLanguage <> mh.UID_DialogMultiLanguage AND mol.ModuleName <> moh.ModuleName AND EXISTS(67    SELECT TOP 1 168    FROM QBMModuleDependCollection co69    WHERE70    LEFT(co.UID_ModuleFollower, 3) = moh.ModuleName AND71    LEFT(co.UID_ModulePredecessor, 3) = mol.ModuleName) AND EXISTS(72    SELECT TOP 1 173    FROM @DieDoppelten1 je74    WHERE75      ml.UID_DialogColumn = je.UID_DialogColumn AND ml.UID_DialogCulture = je.UID_DialogCulture AND ml.EntryKey = je.EntryKey)76    SELECT @CountRows = @@ROWCOUNT77    IF @DebugSwitch > 078    BEGIN79      print 'lineare gelöscht ' + str(@CountRows)80    END81    IF EXISTS(82      SELECT TOP 1 183      FROM sys.tables t84      WHERE85        t.name = 'MDKMLGuidReplace')86    BEGIN87      INSERT INTO MDKMLGuidReplace(UID_DialogMultiLanguageNew,88      UID_DialogMultiLanguageOld,89      XDateInserted,90      XDateUpdated,91      XUserInserted,92      XUserUpdated)93      SELECT94        UID_DialogMultiLanguageNew,95        UID_DialogMultiLanguageOld,96        XDateInserted,97        XDateUpdated,98        XUserInserted,99        XUserUpdated100      FROM @MDKMLGuidReplace101    END102    DELETE DialogMultiLanguage103    FROM DialogMultiLanguage ml104    JOIN @MDKMLGuidReplace me105      ON ml.UID_DialogMultiLanguage = me.UID_DialogMultiLanguageOld106    INSERT INTO @DieDoppelten2(UID_DialogColumn,107    UID_DialogCulture,108    EntryKey)109    SELECT110      m.UID_DialogColumn,111      m.UID_DialogCulture,112      m.EntryKey113    FROM DialogMultiLanguage m114    GROUP BY m.UID_DialogColumn,115    m.UID_DialogCulture,116    m.EntryKey117    HAVING COUNT(*) > 1118    IF @@ROWCOUNT = 0119    BEGIN120      GOTO endlabel121    END122    IF @ich = 'CCC'123    BEGIN124      DELETE DialogMultiLanguage125      WHERE126        UID_DialogMultiLanguage IN(127      SELECT mh.UID_DialogMultiLanguage128      FROM DialogMultiLanguage ml129      JOIN DialogMultiLanguage mh130        ON ml.UID_DialogColumn = mh.UID_DialogColumn AND ml.UID_DialogCulture = mh.UID_DialogCulture AND ml.EntryKey = mh.EntryKey131      JOIN DialogColumn c132        WITH(readpast)133        ON ml.UID_DialogColumn = c.UID_DialogColumn134      JOIN DialogTable t135        WITH(readpast)136        ON c.UID_DialogTable = t.UID_DialogTable137      JOIN QBMModuleDef mol138        WITH(readpast)139        ON dbo.QBM_FCVGUIDToModuleOwner(ml.UID_DialogMultiLanguage) = mol.ModuleName140      JOIN QBMModuleDef moh141        WITH(readpast)142        ON dbo.QBM_FCVGUIDToModuleOwner(mh.UID_DialogMultiLanguage) = moh.ModuleName143      WHERE144        ml.UID_DialogMultiLanguage <> mh.UID_DialogMultiLanguage AND mol.ModuleName <> moh.ModuleName AND NOT EXISTS(145      SELECT TOP 1 1146      FROM QBMModuleDependCollection co147      WHERE148      LEFT(co.UID_ModuleFollower, 3) = mol.ModuleName AND149      LEFT(co.UID_ModulePredecessor, 3) = moh.ModuleName) AND dbo.QBM_FCVStringPadLeft(dbo.QBM_FCVIntToString(CASE mol.ModuleName150      WHEN @ich THEN151      0152      ELSE mol.SortOrder153      END), 16, '0') + CASE mol.ModuleName154      WHEN @ich THEN155      ''156      ELSE mol.ModuleName157      END < dbo.QBM_FCVStringPadLeft(dbo.QBM_FCVIntToString(moh.SortOrder), 16, '0') + moh.ModuleName AND EXISTS(158      SELECT TOP 1 1159      FROM @DieDoppelten2 je160      WHERE161        ml.UID_DialogColumn = je.UID_DialogColumn AND ml.UID_DialogCulture = je.UID_DialogCulture AND ml.EntryKey = je.EntryKey))162      SELECT @CountRows = @@ROWCOUNT163      IF @DebugSwitch > 0164      BEGIN165        print 'nicht-Lineare gelöscht ' + str(@CountRows)166      END167    END168    INSERT INTO @DieDoppelten3(UID_DialogColumn,169    UID_DialogCulture,170    EntryKey)171    SELECT172      m.UID_DialogColumn,173      m.UID_DialogCulture,174      m.EntryKey175    FROM DialogMultiLanguage m176    GROUP BY m.UID_DialogColumn,177    m.UID_DialogCulture,178    m.EntryKey179    HAVING COUNT(*) > 1180    IF @@ROWCOUNT = 0181    BEGIN182      GOTO endlabel183    END184    DELETE DialogMultiLanguage185    WHERE186      UID_DialogMultiLanguage IN(187    SELECT mh.UID_DialogMultiLanguage188    FROM DialogMultiLanguage ml189    JOIN DialogMultiLanguage mh190      ON ml.UID_DialogColumn = mh.UID_DialogColumn AND ml.UID_DialogCulture = mh.UID_DialogCulture AND ml.EntryKey = mh.EntryKey191    JOIN DialogColumn c192      ON ml.UID_DialogColumn = c.UID_DialogColumn193    JOIN DialogTable t194      WITH(readpast)195      ON c.UID_DialogTable = t.UID_DialogTable196    JOIN QBMModuleDef mol197      ON dbo.QBM_FCVGUIDToModuleOwner(ml.UID_DialogMultiLanguage) = mol.ModuleName198    JOIN QBMModuleDef moh199      ON dbo.QBM_FCVGUIDToModuleOwner(mh.UID_DialogMultiLanguage) = moh.ModuleName200    WHERE201      ml.UID_DialogMultiLanguage <> mh.UID_DialogMultiLanguage AND mol.ModuleName = @ich AND moh.ModuleName = @ich AND dbo.QBM_FCVDatetimeToString202    (isnull(ml.XDateUpdated, '1899-12-30')) + ml.UID_DialogMultiLanguage < dbo.QBM_FCVDatetimeToString(isnull(mh.XDateUpdated,203    '1899-12-30')) + mh.UID_DialogMultiLanguage AND EXISTS(204    SELECT TOP 1 1205    FROM @DieDoppelten3 je206    WHERE207      ml.UID_DialogColumn = je.UID_DialogColumn AND ml.UID_DialogCulture = je.UID_DialogCulture AND ml.EntryKey = je.EntryKey))208    SELECT @CountRows = @@ROWCOUNT209    IF @DebugSwitch > 0210    BEGIN211      print 'echte Doubletten gelöscht ' + str(@CountRows)212    END213  END TRY214  BEGIN CATCH215    EXEC QBM_PSessionErrorAdd DEFAULT216    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()217    RAISERROR(@Rethrow,218    18,219    1)220      WITH NOWAIT221  END CATCH222  endLabel:223  RETURN224END

Open raw exported source

1   create   procedure QBM_PMultilanguageDoubleClear as begin declare @ich varchar(3) declare @DebugSwitch int = 0 declare @CountRows int declare2 @DieDoppelten1 QBM_YMultilanguageDouble   declare @DieDoppelten2 QBM_YMultilanguageDouble declare @DieDoppelten3 QBM_YMultilanguageDouble declare @MDKMLGuidReplace3 table (UID_DialogMultiLanguageNew varchar(38) collate database_default  , UID_DialogMultiLanguageOld varchar(38) collate database_default  , XUserInserted4 nvarchar(64) collate database_default , XUserUpdated nvarchar(64) collate database_default , XDateInserted datetime , XDateUpdated datetime ) SET XACT_ABORT5 OFF BEGIN TRY set nocount on select @ich = dbo.QBM_FGIDBOwner()  insert into @DieDoppelten1 (UID_DialogColumn, UID_DialogCulture, EntryKey) select m.UID_DialogColumn6, m.UID_DialogCulture, m.EntryKey from DialogMultiLanguage m group by m.UID_DialogColumn, m.UID_DialogCulture, m.EntryKey having COUNT(*) > 1 if @@ROWCOUNT7 = 0 begin goto endlabel end insert into @MDKMLGuidReplace(UID_DialogMultiLanguageNew, UID_DialogMultiLanguageOld , XDateInserted, XDateUpdated, XUserInserted8, XUserUpdated ) select ml.UID_DialogMultiLanguage, mh.UID_DialogMultiLanguage , mh.XDateInserted, mh.XDateUpdated, mh.XUserInserted, mh.XUserUpdated  9from DialogMultiLanguage ml join DialogMultiLanguage mh on ml.UID_DialogColumn = mh.UID_DialogColumn and ml.UID_DialogCulture = mh.UID_DialogCulture and10 ml.EntryKey = mh.EntryKey join DialogColumn c with (readpast) on ml.UID_DialogColumn = c.UID_DialogColumn join DialogTable t with (readpast) on c.UID_DialogTable11 = t.UID_DialogTable join QBMModuleDef mol with (readpast) on dbo.QBM_FCVGUIDToModuleOwner(ml.UID_DialogMultiLanguage) = mol.ModuleName join QBMModuleDef12 moh with (readpast) on dbo.QBM_FCVGUIDToModuleOwner(mh.UID_DialogMultiLanguage) = moh.ModuleName where ml.UID_DialogMultiLanguage <> mh.UID_DialogMultiLanguage13 and  mol.ModuleName <> moh.ModuleName and  exists (Select top 1 1 from QBMModuleDependCollection co where left(co.UID_ModuleFollower, 3) = moh.ModuleName14 and left(co.UID_ModulePredecessor, 3) = mol.ModuleName )  and exists (Select top 1 1 from @DieDoppelten1 je where ml.UID_DialogColumn = je.UID_DialogColumn15 and ml.UID_DialogCulture = je.UID_DialogCulture and ml.EntryKey = je.EntryKey ) select @CountRows = @@ROWCOUNT if @DebugSwitch > 0 begin print 'lineare gelöscht '16 + str(@CountRows) end  if exists (select top 1 1 from sys.tables t where t.name = 'MDKMLGuidReplace' ) begin insert into MDKMLGuidReplace(UID_DialogMultiLanguageNew17, UID_DialogMultiLanguageOld, XDateInserted, XDateUpdated, XUserInserted, XUserUpdated) select UID_DialogMultiLanguageNew, UID_DialogMultiLanguageOld, 18XDateInserted, XDateUpdated, XUserInserted, XUserUpdated from @MDKMLGuidReplace end delete DialogMultiLanguage from DialogMultiLanguage ml join @MDKMLGuidReplace19 me on ml.UID_DialogMultiLanguage = me.UID_DialogMultiLanguageOld  insert into @DieDoppelten2 (UID_DialogColumn, UID_DialogCulture, EntryKey) select m.UID_DialogColumn20, m.UID_DialogCulture, m.EntryKey from DialogMultiLanguage m group by m.UID_DialogColumn, m.UID_DialogCulture, m.EntryKey having COUNT(*) > 1 if @@ROWCOUNT21 = 0 begin goto endlabel end if @ich = 'CCC' begin delete DialogMultiLanguage   where UID_DialogMultiLanguage in ( select mh.UID_DialogMultiLanguage  from22 DialogMultiLanguage ml join DialogMultiLanguage mh on ml.UID_DialogColumn = mh.UID_DialogColumn and ml.UID_DialogCulture = mh.UID_DialogCulture and ml.EntryKey23 = mh.EntryKey join DialogColumn c with (readpast) on ml.UID_DialogColumn = c.UID_DialogColumn join DialogTable t with (readpast) on c.UID_DialogTable 24= t.UID_DialogTable join QBMModuleDef mol with (readpast) on dbo.QBM_FCVGUIDToModuleOwner(ml.UID_DialogMultiLanguage) = mol.ModuleName join QBMModuleDef25 moh with (readpast) on dbo.QBM_FCVGUIDToModuleOwner(mh.UID_DialogMultiLanguage) = moh.ModuleName where ml.UID_DialogMultiLanguage <> mh.UID_DialogMultiLanguage26 and  mol.ModuleName <> moh.ModuleName and  not exists (Select top 1 1 from QBMModuleDependCollection co where left(co.UID_ModuleFollower, 3) = mol.ModuleName27 and left(co.UID_ModulePredecessor, 3) = moh.ModuleName )    and dbo.QBM_FCVStringPadLeft(dbo.QBM_FCVIntToString( case mol.ModuleName when @ich then 0 28else mol.SortOrder end ), 16, '0') + case mol.ModuleName when @ich then '' else mol.ModuleName end < dbo.QBM_FCVStringPadLeft(dbo.QBM_FCVIntToString(moh.SortOrder29), 16, '0') + moh.ModuleName  and exists (Select top 1 1 from @DieDoppelten2 je where ml.UID_DialogColumn = je.UID_DialogColumn and ml.UID_DialogCulture30 = je.UID_DialogCulture and ml.EntryKey = je.EntryKey ) ) select @CountRows = @@ROWCOUNT if @DebugSwitch > 0 begin print 'nicht-Lineare gelöscht ' + str31(@CountRows) end end insert into @DieDoppelten3 (UID_DialogColumn, UID_DialogCulture, EntryKey) select m.UID_DialogColumn, m.UID_DialogCulture, m.EntryKey32 from DialogMultiLanguage m group by m.UID_DialogColumn, m.UID_DialogCulture, m.EntryKey having COUNT(*) > 1 if @@ROWCOUNT = 0 begin goto endlabel end 33delete DialogMultiLanguage where UID_DialogMultiLanguage in ( select mh.UID_DialogMultiLanguage  from DialogMultiLanguage ml join DialogMultiLanguage mh34 on ml.UID_DialogColumn = mh.UID_DialogColumn and ml.UID_DialogCulture = mh.UID_DialogCulture and ml.EntryKey = mh.EntryKey join DialogColumn c on ml.UID_DialogColumn35 = c.UID_DialogColumn join DialogTable t with (readpast) on c.UID_DialogTable = t.UID_DialogTable join QBMModuleDef mol on dbo.QBM_FCVGUIDToModuleOwner36(ml.UID_DialogMultiLanguage) = mol.ModuleName join QBMModuleDef moh on dbo.QBM_FCVGUIDToModuleOwner(mh.UID_DialogMultiLanguage) = moh.ModuleName where 37ml.UID_DialogMultiLanguage <> mh.UID_DialogMultiLanguage and  mol.ModuleName = @ich and moh.ModuleName = @ich   and dbo.QBM_FCVDatetimeToString(isnull(ml.XDateUpdated38, '1899-12-30')) + ml.UID_DialogMultiLanguage < dbo.QBM_FCVDatetimeToString(isnull(mh.XDateUpdated, '1899-12-30')) + mh.UID_DialogMultiLanguage  and exists39 (Select top 1 1 from @DieDoppelten3 je where ml.UID_DialogColumn = je.UID_DialogColumn and ml.UID_DialogCulture = je.UID_DialogCulture and ml.EntryKey40 = je.EntryKey ) ) select @CountRows = @@ROWCOUNT if @DebugSwitch > 0 begin print 'echte Doubletten gelöscht ' + str(@CountRows) end END TRY BEGIN CATCH41 exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH 42endLabel:  return end 43