Back to OIM Explorer

dbo.QBM_PMissingDisplayRightFill

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 11.520 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_FCVElementToObjectKey1 source text reference
references source dbo.QBM_FCVStringToGUID source text reference
references source dbo.QBM_FCVStringToHash source text reference
references source dbo.QBM_FCVStringToIndent source text reference
references source dbo.QBM_PJournal source text reference
references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

dbo.QBM_ZMissingDisplayRightFill

Complete Source

1CREATE PROCEDURE QBM_PMissingDisplayRightFill(2  @UID_DialogTable varchar(38)3)4AS5BEGIN6  DECLARE @TableName varchar(30)7  DECLARE @hashes TABLE(HashWithoutJoin varbinary(20) index Hashes1(HashWithoutJoin))8  DECLARE @JoinConditionConcat nvarchar(max)9  DECLARE @HashWithoutJoin varbinary(20)10  DECLARE @zwischen TABLE(UID_DialogGroup varchar(38) collate database_default,11  UID_DialogColumn varchar(38) collate database_default,12  ColumnName varchar(30) collate database_default,13  IsTableRightExisting BIT,14  SelectWhereClause nvarchar(max) collate database_default,15  ChildTableName varchar(30) collate database_default,16  JoinCondition nvarchar(max) collate database_default,17  HashWithoutJoin varbinary(20),18  BaseClauseEmpty BIT DEFAULT 0,19  index zwischen1(HashWithoutJoin))20  DECLARE @erg TABLE(UID_DialogGroup varchar(38) collate database_default,21  UID_DialogColumn varchar(38) collate database_default,22  ColumnName varchar(30) collate database_default,23  IsTableRightExisting BIT,24  SelectWhereClause nvarchar(max) collate database_default,25  ChildTableName varchar(30) collate database_default,26  UID_QBMMissingDisplayRight varchar(38) collate database_default,27  UID_DialogTableChild varchar(38) collate database_default,28  XObjectKey varchar(138) collate database_default)29  DECLARE @DebugSwitch int = 030  DECLARE @DebugLevel varchar(1) = 'W'31  DECLARE @DebugMessage nvarchar(max)32  DECLARE @Anzahl int33  BEGIN TRY34    SET nocount35      ON36    SELECT TOP 1 @TableName = t.TableName37    FROM DialogTable t38      WITH(readpast)39    WHERE40      t.UID_DialogTable = @UID_DialogTable41    INSERT INTO @zwischen(UID_DialogGroup,42    UID_DialogColumn,43    ColumnName,44    IsTableRightExisting,45    SelectWhereClause,46    ChildTableName,47    JoinCondition,48    HashWithoutJoin,49    BaseClauseEmpty)50    SELECT51      DISTINCT soll.UID_DialogGroup,52      soll.UID_DialogColumn,53      soll.ParentDisplayColumn,54      sign(len(isnull(istT.UID_DialogTable, ''))),55      soll.SelectWhereClause,56      soll.ChildTableName,57      soll.JoinCondition,58      dbo.QBM_FCVStringToHash(CONCAT(soll.UID_DialogGroup, soll.UID_DialogColumn, soll.ParentDisplayColumn,59      str(sign(len(isnull(istT.UID_DialogTable, '')))), soll.SelectWhereClause, soll.ChildTableName)),60      soll.BaseClauseEmpty61    FROM(62    SELECT63      tr.UID_DialogTable,64      cr.ColumnName,65      ref.TableNameFK AS TablenameParent,66      @UID_DialogTable AS UID_DialogTableDisplay,67      cgr.UID_DialogGroup,68      cr.UID_DialogColumn AS UID_DialogColumnChild,69      @UID_DialogTable AS UID_DialogTableParent,70      ref.UID_DialogColumnDisplay AS UID_DialogColumn,71      ref.ColumnNameDisplay AS ParentDisplayColumn,72    CASE73      WHEN tgr.SelectWhereClause > ' ' THEN74    075    ELSE 176    END AS BaseClauseEmpty,77    CASE78      WHEN tr.TableName <> @TableName THEN79    'exists (select top 1 1  80			from ' + tr.TableName + ' with (readpast)81			where ' + CASE82      WHEN tgr.SelectWhereClause IS NULL THEN83    ''84    ELSE '(85' + dbo.QBM_FCVStringToIndent(isnull(tgr.SelectWhereClause, '1=1'), 5) + '86					)87			and '88    END + '@@joinCondition@@' + '89		) '90    ELSE r.ParentColumn + ' in ( select ' + cr.ColumnName + ' from ' + tr.TableName + ' with (readpast)  where ' + isnull(tgr.SelectWhereClause,91    '1=1') + ') /* old style */ '92    END AS SelectWhereClause,93    tr.TableName AS ChildTableName,94    isnull(tgr.SelectWhereClause, '1=1') AS KernelClause,95    CASE96      WHEN tr.TableName <> @TableName THEN97    tr.TableName + '.' + cr.ColumnName + ' = ' + @TableName + '.' + r.ParentColumn98    ELSE ''99    END AS JoinCondition100    FROM(101    SELECT102      tabref.TableName AS TableNameFK, colref.ColumnName AS ColumnNameDisplay, colref.UID_DialogColumn AS UID_DialogColumnDisplay103    FROM DialogTable tabref104      WITH(readpast)105    JOIN DialogColumn colref106      WITH(readpast)107      ON tabref.UID_DialogTable = @UID_DialogTable AND colref.UID_DialogTable = @UID_DialogTable108    JOIN DialogColumn colpk109      WITH(readpast)110      ON colpk.UID_DialogTable = @UID_DialogTable AND colpk.IsPKMember = 1111    WHERE112      (replace(tabref.DisplayPattern, '??', '%%') LIKE '%[%]' + colref.columnname + N '[%]%' OR replace(tabref.DisplayPatternLong,113    '??', '%%') LIKE '%[%]' + colref.columnname + N '[%]%') AND tabref.IsDeactivatedByPreProcessor = 0 AND colref.IsDeactivatedByPreProcessor = 0114    GROUP BY tabref.TableName, colref.ColumnName, colref.UID_DialogColumn, colpk.UID_DialogColumn, colpk.ColumnName) AS ref115    JOIN QBM_VQBMRelationALL r116      ON r.UID_DialogTableParent = @UID_DialogTable117    JOIN DialogTableGroupRight tgr118      WITH(readpast)119      ON tgr.UID_DialogTable = r.UID_DialogTableChild AND tgr.CanSee = 1120    JOIN DialogTable tr121      WITH(readpast)122      ON tgr.UID_DialogTable = tr.UID_DialogTable AND tr.IsDeactivatedByPreProcessor = 0123    JOIN DialogColumnGroupRight cgr124      WITH(readpast)125      ON cgr.UID_DialogColumn = r.UID_ChildColumn AND tgr.UID_DialogGroup = cgr.UID_DialogGroup AND cgr.CanSee = 1126    JOIN DialogColumn cr127      WITH(readpast)128      ON cr.UID_DialogColumn = cgr.UID_DialogColumn AND cr.IsDeactivatedByPreProcessor = 0 AND(cr.IsForeignKey = 1 OR cr.IsDynamicFK = 1)) AS soll129    LEFT130    OUTER131    JOIN(132    SELECT133      co.UID_DialogGroup,134      tgr.UID_DialogTable,135      co.UID_DialogGroupParent136    FROM DialogGroupCollection co137      WITH(readpast)138    JOIN DialogTableGroupRight tgr139      WITH(readpast)140      ON co.UID_DialogGroupParent = tgr.UID_DialogGroup AND tgr.UID_DialogTable = @UID_DialogTable AND tgr.CanSee = 1) AS istT141      ON soll.UID_DialogTableParent = istT.UID_DialogTable AND soll.UID_DialogGroup = istT.UID_DialogGroup142    LEFT143    OUTER144    JOIN(145    SELECT146      co.UID_DialogGroup,147      tgr.UID_DialogTable,148      co.UID_DialogGroupParent,149      cgr.UID_DialogColumn150    FROM DialogGroupCollection co151      WITH(readpast)152    JOIN DialogTableGroupRight tgr153      WITH(readpast)154      ON co.UID_DialogGroupParent = tgr.UID_DialogGroup AND tgr.UID_DialogTable = @UID_DialogTable AND tgr.CanSee = 1155    JOIN DialogColumnGroupRight cgr156      WITH(readpast)157      ON tgr.UID_DialogGroup = cgr.UID_DialogGroup AND cgr.CanSee = 1158    JOIN DialogColumn c159      WITH(readpast)160      ON cgr.UID_DialogColumn = c.UID_DialogColumn AND c.UID_DialogTable = tgr.UID_DialogTable) AS istC161      ON soll.UID_DialogTableParent = istC.UID_DialogTable AND soll.UID_DialogGroup = istC.UID_DialogGroup AND soll.UID_DialogColumn = istC.UID_DialogColumn162    WHERE163      istC.UID_DialogColumn IS NULL OR istT.UID_DialogTable > ' '164    INSERT INTO @erg(UID_DialogGroup,165    UID_DialogColumn,166    ColumnName,167    IsTableRightExisting,168    SelectWhereClause,169    ChildTableName)170    SELECT171      DISTINCT UID_DialogGroup,172      UID_DialogColumn,173      ColumnName,174      IsTableRightExisting,175      SelectWhereClause,176      ChildTableName177    FROM @zwischen178    WHERE179      JoinCondition = ''180    INSERT INTO @erg(UID_DialogGroup,181    UID_DialogColumn,182    ColumnName,183    IsTableRightExisting,184    SelectWhereClause,185    ChildTableName)186    SELECT187      DISTINCT z.UID_DialogGroup,188      z.UID_DialogColumn,189      z.ColumnName,190      z.IsTableRightExisting,191      replace(z.SelectWhereClause,192      '@@joincondition@@',193      z.JoinCondition),194      z.ChildTableName195    FROM @zwischen z196    WHERE197      z.JoinCondition > ' ' AND z.HashWithoutJoin IN(198    SELECT g.HashWithoutJoin199    FROM @zwischen g200    GROUP BY g.HashWithoutJoin201    HAVING count(*) = 1)202    INSERT INTO @hashes(HashWithoutJoin)203    SELECT204      DISTINCT z.HashWithoutJoin205    FROM @zwischen z206    WHERE207      z.JoinCondition > ' ' AND z.HashWithoutJoin IN(208    SELECT g.HashWithoutJoin209    FROM @zwischen g210    GROUP BY g.HashWithoutJoin211    HAVING count(*) > 1) AND z.BaseClauseEmpty = 0212    SELECT @HashWithoutJoin = 0x01213    WHILE @HashWithoutJoin IS NOT NULL214    BEGIN215      SELECT @HashWithoutJoin = NULL216      SELECT TOP 1 @HashWithoutJoin = h.HashWithoutJoin217      FROM @hashes h218      ORDER BY h.HashWithoutJoin219      IF @HashWithoutJoin IS NULL220      BEGIN221        CONTINUE222      END223      SELECT224        @JoinConditionConcat = CONCAT('(',225        string_agg(convert(nvarchar(max), z.JoinCondition), N ' or  ') within226        GROUP(227      ORDER BY z.JoinCondition),228      ')')229      FROM @zwischen z230      WHERE231        z.HashWithoutJoin = @HashWithoutJoin232      INSERT INTO @erg(UID_DialogGroup,233      UID_DialogColumn,234      ColumnName,235      IsTableRightExisting,236      SelectWhereClause,237      ChildTableName)238      SELECT239        TOP 1 z.UID_DialogGroup,240        z.UID_DialogColumn,241        z.ColumnName,242        z.IsTableRightExisting,243        replace(z.SelectWhereClause,244        '@@joincondition@@',245        @JoinConditionConcat),246        z.ChildTableName247      FROM @zwischen z248      WHERE249        z.HashWithoutJoin = @HashWithoutJoin250      DELETE @hashes251      WHERE252        HashWithoutJoin = @HashWithoutJoin253    END254    DELETE @hashes255    INSERT INTO @hashes(HashWithoutJoin)256    SELECT257      DISTINCT z.HashWithoutJoin258    FROM @zwischen z259    WHERE260      z.JoinCondition > ' ' AND z.HashWithoutJoin IN(261    SELECT g.HashWithoutJoin262    FROM @zwischen g263    GROUP BY g.HashWithoutJoin264    HAVING count(*) > 1) AND z.BaseClauseEmpty = 1265    SELECT @HashWithoutJoin = 0x01266    WHILE @HashWithoutJoin IS NOT NULL267    BEGIN268      SELECT @HashWithoutJoin = NULL269      SELECT TOP 1 @HashWithoutJoin = h.HashWithoutJoin270      FROM @hashes h271      IF @HashWithoutJoin IS NULL272      BEGIN273        CONTINUE274      END275      SELECT276        @JoinConditionConcat = string_agg(convert(nvarchar(max), N '277		select top 1 1  278			from @Tablename@ with (readpast)279			where ' + z.JoinCondition280        ),281        N '282		283		union all 284				') within285        GROUP(286      ORDER BY z.JoinCondition)287      FROM @zwischen z288      WHERE289        z.HashWithoutJoin = @HashWithoutJoin290      INSERT INTO @erg(UID_DialogGroup,291      UID_DialogColumn,292      ColumnName,293      IsTableRightExisting,294      SelectWhereClause,295      ChildTableName)296      SELECT297        TOP 1 z.UID_DialogGroup,298        z.UID_DialogColumn,299        z.ColumnName,300        z.IsTableRightExisting,301        'exists (302			' + replace(@JoinConditionConcat,303        '@Tablename@',304        z.ChildTableName) + '305		)',306        z.ChildTableName307      FROM @zwischen z308      WHERE309        z.HashWithoutJoin = @HashWithoutJoin310      DELETE @hashes311      WHERE312        HashWithoutJoin = @HashWithoutJoin313    END314    UPDATE @erg315    SET UID_DialogTableChild = t.UID_DialogTable316    FROM @erg e317    JOIN DialogTable t318      WITH(readpast)319      ON e.ChildTableName = t.TableName320    UPDATE @erg321    SET UID_QBMMissingDisplayRight = dbo.QBM_FCVStringToGUID('',322    CONCAT(e.UID_DialogGroup, e.UID_DialogColumn, e.UID_DialogTableChild, e.SelectWhereClause)),323    XObjectKey = dbo.QBM_FCVElementToObjectKey1('QBMMissingDisplayRight',324    '',325    dbo.QBM_FCVStringToGUID('', CONCAT(e.UID_DialogGroup, e.UID_DialogColumn, e.UID_DialogTableChild, e.SelectWhereClause)))326    FROM @erg e327    BEGIN328      TRANSACTION329      INSERT INTO QBMMissingDisplayRight330        WITH(tablockX)(UID_QBMMissingDisplayRight,331      UID_DialogGroup,332      UID_DialogColumn,333      UID_DialogTableChild,334      IsTableRightExisting,335      SelectWhereClause,336      XObjectKey)337      SELECT338        UID_QBMMissingDisplayRight,339        UID_DialogGroup,340        UID_DialogColumn,341        UID_DialogTableChild,342        IsTableRightExisting,343        SelectWhereClause,344        XObjectKey345      FROM @erg e346      WHERE347        NOT EXISTS(348      SELECT TOP 1 1349      FROM QBMMissingDisplayRight v350        WITH(readpast)351      WHERE352        v.UID_QBMMissingDisplayRight = e.UID_QBMMissingDisplayRight)353      SELECT @Anzahl = @@ROWCOUNT354      IF @DebugSwitch > 0355      BEGIN356        print 'eingefügt ' + @TableName + str(@anzahl)357      END358      UPDATE QBMMissingDisplayRight359      SET IsTableRightExisting = e.IsTableRightExisting,360      SelectWhereClause = e.SelectWhereClause361      FROM QBMMissingDisplayRight mr362        WITH(readpast)363      JOIN @erg e364        ON mr.UID_QBMMissingDisplayRight = e.UID_QBMMissingDisplayRight365      WHERE366        isnull(mr.IsTableRightExisting,367      0) <> isnull(e.IsTableRightExisting,368      0) OR isnull(mr.SelectWhereClause,369      '') <> isnull(e.SelectWhereClause,370      '')371      SELECT @Anzahl = @@ROWCOUNT372      IF @DebugSwitch > 0373      BEGIN374        print 'geändert ' + @TableName + str(@Anzahl)375      END376      DELETE QBMMissingDisplayRight377      FROM QBMMissingDisplayRight mr378      JOIN DialogColumn c379        WITH(readpast)380        ON mr.UID_DialogColumn = c.UID_DialogColumn381      WHERE382        c.UID_DialogTable = @UID_DialogTable AND NOT EXISTS(383      SELECT TOP 1 1384      FROM @erg e385      WHERE386        e.UID_QBMMissingDisplayRight = mr.UID_QBMMissingDisplayRight)387      SELECT @Anzahl = @@ROWCOUNT388      IF @DebugSwitch > 0389      BEGIN390        print 'gelöscht ' + @TableName + str(@Anzahl)391      END392      COMMIT TRANSACTION393    END TRY394    BEGIN CATCH395      EXEC QBM_PSessionErrorAdd DEFAULT396      EXEC QBM_PJournal 'SessionError',397        @@procid,398      'E',399        @DebugLevel400      RAISERROR('',401      18,402      1)403        WITH NOWAIT404    END CATCH405    endLabel:406  END

Open raw exported source

1   create   procedure QBM_PMissingDisplayRightFill ( @UID_DialogTable varchar(38) ) as begin declare @TableName varchar(30) declare @hashes table2 (HashWithoutJoin varbinary(20) index Hashes1 (HashWithoutJoin) ) declare @JoinConditionConcat nvarchar(max) declare @HashWithoutJoin varbinary(20) declare3 @zwischen table ( UID_DialogGroup varchar(38) collate database_default , UID_DialogColumn varchar(38) collate database_default , ColumnName varchar(304) collate database_default   , IsTableRightExisting bit  , SelectWhereClause nvarchar(max) collate database_default , ChildTableName varchar(30) collate5 database_default , JoinCondition nvarchar(max) collate database_default , HashWithoutJoin varbinary(20) , BaseClauseEmpty bit default 0 , index zwischen16 (HashWithoutJoin) ) declare @erg table ( UID_DialogGroup varchar(38) collate database_default , UID_DialogColumn varchar(38) collate database_default 7, ColumnName varchar(30) collate database_default , IsTableRightExisting bit  , SelectWhereClause nvarchar(max) collate database_default , ChildTableName8 varchar(30) collate database_default  , UID_QBMMissingDisplayRight varchar(38) collate database_default  , UID_DialogTableChild varchar(38) collate database_default9  , XObjectKey varchar(138) collate database_default   ) declare @DebugSwitch int = 0 declare @DebugLevel varchar(1) = 'W' declare @DebugMessage nvarchar10(max) declare @Anzahl int BEGIN TRY set nocount on select top 1 @TableName = t.TableName from DialogTable t with (readpast) where t.UID_DialogTable = @UID_DialogTable11  insert into @zwischen(UID_DialogGroup, UID_DialogColumn, ColumnName, IsTableRightExisting, SelectWhereClause,ChildTableName, JoinCondition, HashWithoutJoin12 , BaseClauseEmpty ) select distinct soll.UID_DialogGroup , soll.UID_DialogColumn , soll.ParentDisplayColumn , sign(len(isnull(istT.UID_DialogTable, ''13)))  , soll.SelectWhereClause , soll.ChildTableName , soll.JoinCondition , dbo.QBM_FCVStringToHash( concat( soll.UID_DialogGroup , soll.UID_DialogColumn14 , soll.ParentDisplayColumn , str(sign(len(isnull(istT.UID_DialogTable, '')))) , soll.SelectWhereClause , soll.ChildTableName ) ) , soll.BaseClauseEmpty15 from ( select  tr.UID_DialogTable , cr.ColumnName  , ref.TableNameFK as TablenameParent , @UID_DialogTable as UID_DialogTableDisplay , cgr.UID_DialogGroup16 , cr.UID_DialogColumn as UID_DialogColumnChild , @UID_DialogTable as UID_DialogTableParent , ref.UID_DialogColumnDisplay as UID_DialogColumn , ref.ColumnNameDisplay17 as ParentDisplayColumn , case when tgr.SelectWhereClause > ' ' then 0 else 1 end as BaseClauseEmpty ,  case  when tr.TableName <> @TableName then 'exists (select top 1 1  18			from '19 + tr.TableName + ' with (readpast)20			where '  + case when tgr.SelectWhereClause is null then '' else '(21' + dbo.QBM_FCVStringToIndent( isnull(tgr.SelectWhereClause22, '1=1'), 5) + '23					)24			and ' end + '@@joinCondition@@' + '25		) '  else  r.ParentColumn + ' in ( select ' + cr.ColumnName + ' from ' + tr.TableName26 + ' with (readpast)  where ' + isnull(tgr.SelectWhereClause, '1=1') + ') /* old style */ '  end as SelectWhereClause , tr.TableName as ChildTableName 27, isnull(tgr.SelectWhereClause, '1=1') as KernelClause  , case  when tr.TableName <> @TableName then tr.TableName + '.' + cr.ColumnName + ' = ' + @TableName28 + '.' + r.ParentColumn else '' end as JoinCondition    from ( select tabref.TableName as TableNameFK, colref.ColumnName as ColumnNameDisplay, colref.UID_DialogColumn29 as UID_DialogColumnDisplay    from DialogTable tabref with (readpast) join DialogColumn colref with (readpast) on tabref.UID_DialogTable = @UID_DialogTable30 and colref.UID_DialogTable = @UID_DialogTable join DialogColumn colpk with (readpast) on colpk.UID_DialogTable = @UID_DialogTable and colpk.IsPKMember31 = 1 where ( replace(tabref.DisplayPattern, '??', '%%') like '%[%]' + colref.columnname + N'[%]%' or replace(tabref.DisplayPatternLong, '??', '%%') like32 '%[%]' + colref.columnname + N'[%]%' ) and tabref.IsDeactivatedByPreProcessor = 0 and colref.IsDeactivatedByPreProcessor = 0 group by tabref.TableName33 , colref.ColumnName , colref.UID_DialogColumn, colpk.UID_DialogColumn, colpk.ColumnName ) as ref join QBM_VQBMRelationALL r on r.UID_DialogTableParent34 = @UID_DialogTable join DialogTableGroupRight tgr with (readpast) on tgr.UID_DialogTable = r.UID_DialogTableChild and tgr.CanSee = 1 join DialogTable 35tr with (readpast) on tgr.UID_DialogTable = tr.UID_DialogTable and tr.IsDeactivatedByPreProcessor = 0 join DialogColumnGroupRight cgr with (readpast) on36 cgr.UID_DialogColumn = r.UID_ChildColumn and tgr.UID_DialogGroup = cgr.UID_DialogGroup and cgr.CanSee = 1 join DialogColumn cr with (readpast) on cr.UID_DialogColumn37 = cgr.UID_DialogColumn and cr.IsDeactivatedByPreProcessor = 0 and (cr.IsForeignKey = 1 or cr.IsDynamicFK = 1 ) ) as soll left outer join  (  select co.UID_DialogGroup38, tgr.UID_DialogTable, co.UID_DialogGroupParent  from DialogGroupCollection co with (readpast) join DialogTableGroupRight tgr with (readpast) on co.UID_DialogGroupParent39 = tgr.UID_DialogGroup and tgr.UID_DialogTable = @UID_DialogTable and tgr.CanSee = 1     ) as istT on soll.UID_DialogTableParent = istT.UID_DialogTable40 and soll.UID_DialogGroup = istT.UID_DialogGroup  left outer join  (  select co.UID_DialogGroup, tgr.UID_DialogTable, co.UID_DialogGroupParent , cgr.UID_DialogColumn41 from DialogGroupCollection co with (readpast) join DialogTableGroupRight tgr with (readpast) on co.UID_DialogGroupParent = tgr.UID_DialogGroup and tgr.UID_DialogTable42 = @UID_DialogTable and tgr.CanSee = 1 join DialogColumnGroupRight cgr with (readpast) on tgr.UID_DialogGroup = cgr.UID_DialogGroup and cgr.CanSee = 1 43join DialogColumn c with (readpast) on cgr.UID_DialogColumn = c.UID_DialogColumn and c.UID_DialogTable = tgr.UID_DialogTable ) as istC on soll.UID_DialogTableParent44 = istC.UID_DialogTable and soll.UID_DialogGroup = istC.UID_DialogGroup and soll.UID_DialogColumn = istC.UID_DialogColumn where istC.UID_DialogColumn is45 null or istT.UID_DialogTable > ' '  insert into @erg(UID_DialogGroup, UID_DialogColumn, ColumnName, IsTableRightExisting, SelectWhereClause,ChildTableName46 ) select distinct  UID_DialogGroup, UID_DialogColumn, ColumnName, IsTableRightExisting, SelectWhereClause,ChildTableName from @zwischen where JoinCondition47 = '' insert into @erg(UID_DialogGroup, UID_DialogColumn, ColumnName, IsTableRightExisting, SelectWhereClause,ChildTableName ) select distinct  z.UID_DialogGroup48, z.UID_DialogColumn, z.ColumnName, z.IsTableRightExisting , replace(z.SelectWhereClause, '@@joincondition@@', z.JoinCondition) , z.ChildTableName from49 @zwischen z where z.JoinCondition > ' ' and z.HashWithoutJoin in ( select g.HashWithoutJoin from @zwischen g group by g.HashWithoutJoin having count(*50) = 1 )   insert into @hashes(HashWithoutJoin) select distinct z.HashWithoutJoin from @zwischen z where z.JoinCondition > ' ' and z.HashWithoutJoin in 51( select g.HashWithoutJoin from @zwischen g group by g.HashWithoutJoin having count(*) > 1 )  and z.BaseClauseEmpty = 0 select @HashWithoutJoin = 0x01 52while @HashWithoutJoin is not null begin select @HashWithoutJoin = null select top 1 @HashWithoutJoin = h.HashWithoutJoin from @hashes h order by h.HashWithoutJoin53 if @HashWithoutJoin is null begin continue end select @JoinConditionConcat = concat('(' , string_agg ( convert(nvarchar(max),z.JoinCondition )  , N' or  '54 ) within group (order by z.JoinCondition )  , ')' ) from @zwischen z where z.HashWithoutJoin = @HashWithoutJoin insert into @erg(UID_DialogGroup, UID_DialogColumn55, ColumnName, IsTableRightExisting, SelectWhereClause,ChildTableName ) select top 1  z.UID_DialogGroup, z.UID_DialogColumn, z.ColumnName, z.IsTableRightExisting56 , replace(z.SelectWhereClause, '@@joincondition@@', @JoinConditionConcat) , z.ChildTableName from @zwischen z where z.HashWithoutJoin = @HashWithoutJoin57 delete  @hashes  where HashWithoutJoin = @HashWithoutJoin end   delete  @hashes  insert into @hashes(HashWithoutJoin) select distinct z.HashWithoutJoin58 from @zwischen z where z.JoinCondition > ' ' and z.HashWithoutJoin in ( select g.HashWithoutJoin from @zwischen g group by g.HashWithoutJoin having count59(*) > 1 )  and z.BaseClauseEmpty = 1 select @HashWithoutJoin = 0x01 while @HashWithoutJoin is not null begin select @HashWithoutJoin = null select top 601 @HashWithoutJoin = h.HashWithoutJoin from @hashes h if @HashWithoutJoin is null begin continue end select @JoinConditionConcat = string_agg (convert(nvarchar61(max), N'62		select top 1 1  63			from @Tablename@ with (readpast)64			where ' + z.JoinCondition )  , N'65		66		union all 67				' ) within group (order68 by z.JoinCondition)  from @zwischen z where z.HashWithoutJoin = @HashWithoutJoin insert into @erg(UID_DialogGroup, UID_DialogColumn, ColumnName, IsTableRightExisting69, SelectWhereClause,ChildTableName ) select top 1  z.UID_DialogGroup, z.UID_DialogColumn, z.ColumnName, z.IsTableRightExisting , 'exists (70			' + replace71(@JoinConditionConcat, '@Tablename@', z.ChildTableName) + '72		)' , z.ChildTableName from @zwischen z where z.HashWithoutJoin = @HashWithoutJoin delete73  @hashes  where HashWithoutJoin = @HashWithoutJoin end  update @erg set UID_DialogTableChild = t.UID_DialogTable from @erg e join DialogTable t with (readpast74) on e.ChildTableName = t.TableName update @erg set UID_QBMMissingDisplayRight = dbo.QBM_FCVStringToGUID('', CONCAT ( e.UID_DialogGroup, e.UID_DialogColumn75, e.UID_DialogTableChild, e.SelectWhereClause ) ) , XObjectKey = dbo.QBM_FCVElementToObjectKey1('QBMMissingDisplayRight', '' ,dbo.QBM_FCVStringToGUID(''76, CONCAT ( e.UID_DialogGroup, e.UID_DialogColumn, e.UID_DialogTableChild, e.SelectWhereClause ) ) ) from @erg e    begin transaction  insert into QBMMissingDisplayRight77 with (tablockX) (UID_QBMMissingDisplayRight , UID_DialogGroup, UID_DialogColumn, UID_DialogTableChild , IsTableRightExisting, SelectWhereClause , XObjectKey78 ) select UID_QBMMissingDisplayRight , UID_DialogGroup, UID_DialogColumn, UID_DialogTableChild , IsTableRightExisting, SelectWhereClause , XObjectKey from79 @erg e where not exists (select top 1 1 from QBMMissingDisplayRight v with (readpast) where v.UID_QBMMissingDisplayRight = e.UID_QBMMissingDisplayRight80 ) select @Anzahl = @@ROWCOUNT if @DebugSwitch > 0 begin print 'eingefügt ' + @TableName + str(@anzahl) end  update QBMMissingDisplayRight set IsTableRightExisting81 = e.IsTableRightExisting , SelectWhereClause = e.SelectWhereClause from QBMMissingDisplayRight mr with (readpast) join @erg e on mr.UID_QBMMissingDisplayRight82 = e.UID_QBMMissingDisplayRight where isnull(mr.IsTableRightExisting, 0) <> isnull(e.IsTableRightExisting, 0) or isnull(mr.SelectWhereClause, '') <> isnull83(e.SelectWhereClause, '') select @Anzahl = @@ROWCOUNT if @DebugSwitch > 0 begin print 'geändert ' + @TableName + str(@Anzahl) end  delete QBMMissingDisplayRight84 from QBMMissingDisplayRight mr join DialogColumn c with (readpast) on mr.UID_DialogColumn = c.UID_DialogColumn where c.UID_DialogTable = @UID_DialogTable85 and not exists (select top 1 1 from @erg e where e.UID_QBMMissingDisplayRight = mr.UID_QBMMissingDisplayRight ) select @Anzahl = @@ROWCOUNT if @DebugSwitch86 > 0 begin print 'gelöscht ' + @TableName + str(@Anzahl) end commit transaction END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default exec QBM_PJournal87 'SessionError', @@procid, 'E', @DebugLevel RAISERROR ('', 18, 1) WITH NOWAIT END CATCH endLabel: end 88