Back to OIM Explorer

dbo.QBM_PQBMRelationCheck

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 8.074 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_FTRelationValidationInfo 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_PQBMRelationCheck2AS3BEGIN4  DECLARE @CountItems int = 05  DECLARE @DebugSwitch int = 06  DECLARE @XUser nvarchar(64) = object_name(@@procid)7  DECLARE @Xdate datetime = getutcdate()8  SET XACT_ABORT OFF9  BEGIN TRY10    UPDATE QBMRelation11    SET ParentExecuteBy =12    LEFT(v.ValidParentExecuteBy,13    1),14    XDateUpdated = @Xdate,15    XUserUpdated = @XUser16    FROM QBMRelation r17    JOIN DialogColumn cc18      ON r.uid_ChildColumn = cc.UID_DialogColumn19    JOIN DialogTable c20      ON cc.UID_DialogTable = c.UID_DialogTable21    JOIN DialogColumn cp22      ON r.UID_ParentColumn = cp.UID_DialogColumn23    JOIN DialogTable p24      ON cp.UID_DialogTable = p.UID_DialogTable25    JOIN dbo.QBM_FTRelationValidationInfo() v26      ON v.RIType = 'Fix' AND v.Parenttype = p.TableType AND v.ChildType = c.TableType27    WHERE28      v.ValidParentExecuteBy NOT LIKE '%' + r.ParentExecuteBy + '%' AND v.IsValid = 129    SELECT @CountItems = @@ROWCOUNT30    IF @DebugSwitch > 031    BEGIN32      print 'parentexecuteby geändert' + str(@CountItems)33    END34    UPDATE QBMRelation35    SET ChildExecuteBy =36    LEFT(v.ValidChildExecuteBy,37    1),38    XDateUpdated = @Xdate,39    XUserUpdated = @XUser40    FROM QBMRelation r41    JOIN DialogColumn cc42      ON r.UID_ChildColumn = cc.UID_DialogColumn43    JOIN DialogTable c44      ON cc.UID_DialogTable = c.UID_DialogTable45    JOIN DialogColumn cp46      ON r.UID_ParentColumn = cp.UID_DialogColumn47    JOIN DialogTable p48      ON cp.UID_DialogTable = p.UID_DialogTable49    JOIN dbo.QBM_FTRelationValidationInfo() v50      ON v.RIType = 'Fix' AND v.Parenttype = p.TableType AND v.ChildType = c.TableType51    WHERE52      v.ValidChildExecuteBy NOT LIKE '%' + r.ChildExecuteBy + '%' AND v.IsValid = 153    SELECT @CountItems = @@ROWCOUNT54    IF @DebugSwitch > 055    BEGIN56      print 'Childexecuteby' + str(@CountItems)57    END58    UPDATE DialogColumn59    SET isForeignKey = sign(LEN(isnull(r.UID_ChildColumn, ''))),60    XDateUpdated = @Xdate,61    XUserUpdated = @XUser62    FROM DialogColumn c63    LEFT64    OUTER65    JOIN QBMRelation r66      ON r.UID_ChildColumn = c.UID_DialogColumn67    WHERE68      isnull(c.isForeignKey,69    0) <> sign(LEN(isnull(r.UID_ChildColumn, '')))70    UPDATE QBMRelation71    SET IsMNRelation = 0,72    UID_QBMRelationMN = NULL,73    XDateUpdated = @Xdate,74    XUserUpdated = @XUser75    WHERE76      IsMNRelation = 1 OR UID_QBMRelationMN > ' '77    UPDATE QBMRelation78    SET isMNRelation = 1,79    UID_QBMRelationMN = x.MNRelationUID,80    XDateUpdated = @Xdate,81    XUserUpdated = @XUser82    FROM QBMRelation,83    (84    SELECT85      linkscr.UID_QBMRelation,86      linkscr.UID_DialogTableParent,87      linkscr.UID_DialogTableChild AS mnTable,88      rechtscr.UID_DialogTableParent AS mnPartnerTable,89      rechtscr.UID_QBMRelation AS MNRelationUID,90      rechtscr.RelationID AS MNRelationID91    FROM QBM_VQBMRelation linksCR92    JOIN QBM_VQBMRelation rechtsCR93      ON linksCR.UID_DialogTableChild = rechtsCR.UID_DialogTableChild AND linksCR.UID_QBMRelation <> rechtsCR.UID_QBMRelation94    JOIN(95    SELECT96      UID_DialogTable, count(*) AS CountItems97    FROM dialogcolumn98    WHERE99      ispkmember = 1 AND UID_DialogTable IN(100    SELECT r.UID_DialogTableChild101    FROM QBM_VQBMRelation r)102    GROUP BY UID_DialogTable103    HAVING count(*) > 1) AS n1104      ON n1.UID_DialogTable = linkscr.UID_DialogTableChild105    JOIN(106    SELECT107      UID_DialogTableChild, count(*) AS CountItems108    FROM QBM_VQBMRelation109    GROUP BY UID_DialogTableChild110    HAVING count(*) > 1) AS n2111      ON n2.UID_DialogTableChild = linkscr.UID_DialogTableChild112    JOIN(113    SELECT dcr.UID_QBMRelation114    FROM QBMRelation dcr115    JOIN dialogcolumn dc116      ON dcr.uid_childcolumn = dc.uid_dialogcolumn AND dc.isPKMember = 1) AS n3117      ON n3.UID_QBMRelation = linkscr.UID_QBMRelation118    JOIN(119    SELECT dcr.UID_QBMRelation120    FROM QBMRelation dcr121    JOIN dialogcolumn dc122      ON dcr.uid_childcolumn = dc.uid_dialogcolumn AND dc.isPKMember = 1) AS n4123      ON n4.UID_QBMRelation = rechtscr.UID_QBMRelation) AS x124    WHERE125      QBMRelation.UID_QBMRelation = x.UID_QBMRelation AND QBMRelation.ParentExecuteBy IN('T',126    'D',127    'N') AND QBMRelation.IsMNRelation = 0128    UPDATE DialogTable129    SET isMNTable = 0,130    XDateUpdated = @Xdate,131    XUserUpdated = @XUser132    FROM DialogTable t133    WHERE134      t.TableType NOT IN('U',135    'R') AND t.isMNTable = 1 AND NOT EXISTS(136    SELECT TOP 1 1137    FROM QBMRelation r138    JOIN DialogColumn c139      ON r.UID_ChildColumn = c.UID_DialogColumn140    WHERE141      c.UID_DialogTable = t.UID_DialogTable AND r.ismnrelation = 1)142    UPDATE dialogtable143    SET ismntable = 1,144    XDateUpdated = @Xdate,145    XUserUpdated = @XUser146    FROM DialogTable t147    WHERE148      EXISTS(149    SELECT TOP 1 1150    FROM QBMRelation r151    JOIN DialogColumn c152      ON r.UID_ChildColumn = c.UID_DialogColumn153    WHERE154      c.UID_DialogTable = t.UID_DialogTable AND r.ismnrelation = 1) AND t.isMNTable = 0 AND t.TableType NOT IN('U',155    'R')156    UPDATE DialogTable157    SET IsMAllTable = 1,158    XDateUpdated = @Xdate,159    XUserUpdated = @XUser160    FROM DialogTable t161    JOIN QBM_VMAllTables v162      ON t.UID_DialogTable = v.UID_DialogTable163    WHERE164      t.IsMAllTable = 0 AND t.TableType NOT IN('U',165    'R')166    UPDATE DialogTable167    SET IsMAllTable = 0,168    XDateUpdated = @Xdate,169    XUserUpdated = @XUser170    FROM DialogTable t171    LEFT172    OUTER173    JOIN QBM_VMAllTables v174      ON t.UID_DialogTable = v.UID_DialogTable175    WHERE176      t.IsMAllTable = 1 AND v.UID_DialogTable IS NULL AND t.TableType NOT IN('U',177    'R')178    UPDATE DialogColumn179    SET IsMAllKeyMember = 1,180    XDateUpdated = @Xdate,181    XUserUpdated = @XUser182    FROM DialogColumn c183    JOIN QBM_VMAllTables v184      ON c.UID_DialogTable = v.UID_DialogTable AND(c.UID_DialogColumn = v.UID_DialogColumnFK OR c.UID_DialogColumn = v.UID_DialogColumnObjectKey)185    WHERE186      c.IsMAllKeyMember = 0187    UPDATE DialogColumn188    SET IsMAllKeyMember = 0,189    XDateUpdated = @Xdate,190    XUserUpdated = @XUser191    FROM DialogColumn c192    LEFT193    OUTER194    JOIN QBM_VMAllTables v195      ON c.UID_DialogTable = v.UID_DialogTable AND(c.UID_DialogColumn = v.UID_DialogColumnFK OR c.UID_DialogColumn = v.UID_DialogColumnObjectKey)196    WHERE197      c.IsMAllKeyMember = 1 AND v.UID_DialogTable IS NULL198    SELECT @CountItems = 1199    WHILE @CountItems > 0200    BEGIN201      SELECT @CountItems = 0202      UPDATE QBMRelation203      SET parentAllowUpdate = b.parentAllowUpdate,204      parentrestriction = b.parentrestriction,205      XDateUpdated = @Xdate,206      XUserUpdated = @XUser207      FROM QBMRelation,208      QBMRelation b209      WHERE210        QBMRelation.UID_QBMRelationBase = b.UID_QBMRelation AND(QBMRelation.parentAllowUpdate <> b.parentAllowUpdate OR QBMRelation.parentrestriction211      <> b.parentrestriction) AND b.parentAllowUpdate = 0212      SELECT @CountItems = @CountItems + @@rowcount213      UPDATE QBMRelation214      SET ChildAllowUpdate = b.ChildAllowUpdate,215      ChildRestriction = b.ChildRestriction,216      XDateUpdated = @Xdate,217      XUserUpdated = @XUser218      FROM QBMRelation,219      QBMRelation b220      WHERE221        QBMRelation.UID_QBMRelationBase = b.UID_QBMRelation AND(QBMRelation.ChildAllowUpdate <> b.ChildAllowUpdate OR QBMRelation.ChildRestriction222      <> b.ChildRestriction) AND b.ChildAllowUpdate = 0223      SELECT @CountItems = @CountItems + @@rowcount224      UPDATE QBMRelation225      SET parentAllowUpdate = 0,226      XDateUpdated = @Xdate,227      XUserUpdated = @XUser228      FROM QBMRelation,229      QBMRelation b230      WHERE231        QBMRelation.UID_QBMRelationBase = b.UID_QBMRelation AND QBMRelation.parentAllowUpdate <> 0 AND b.parentAllowUpdate = 1 AND b.Parentexecuteby232      = 'T'233      SELECT @CountItems = @CountItems + @@rowcount234      UPDATE QBMRelation235      SET ChildAllowUpdate = 0,236      XDateUpdated = @Xdate,237      XUserUpdated = @XUser238      FROM QBMRelation,239      QBMRelation b240      WHERE241        QBMRelation.UID_QBMRelationBase = b.UID_QBMRelation AND QBMRelation.ChildAllowUpdate <> 0 AND b.ChildAllowUpdate = 1 AND b.Childexecuteby242      = 'T'243      SELECT @CountItems = @CountItems + @@rowcount244      UPDATE QBMRelation245      SET parentAllowUpdate = 1,246      XDateUpdated = @Xdate,247      XUserUpdated = @XUser248      FROM QBMRelation,249      QBMRelation b250      WHERE251        QBMRelation.UID_QBMRelationBase = b.UID_QBMRelation AND QBMRelation.parentAllowUpdate <> 1 AND b.parentAllowUpdate = 1 AND b.Parentexecuteby252      = 'D'253      SELECT @CountItems = @CountItems + @@rowcount254      UPDATE QBMRelation255      SET ChildAllowUpdate = 1,256      XDateUpdated = @Xdate,257      XUserUpdated = @XUser258      FROM QBMRelation,259      QBMRelation b260      WHERE261        QBMRelation.UID_QBMRelationBase = b.UID_QBMRelation AND QBMRelation.ChildAllowUpdate <> 1 AND b.ChildAllowUpdate = 1 AND b.Childexecuteby262      = 'D'263      SELECT @CountItems = @CountItems + @@rowcount264    END265  END TRY266  BEGIN CATCH267    EXEC QBM_PSessionErrorAdd DEFAULT268    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()269    RAISERROR(@Rethrow,270    18,271    1)272      WITH NOWAIT273  END CATCH274  endLabel:275END

Open raw exported source

1  create   procedure QBM_PQBMRelationCheck as begin declare @CountItems int = 0 declare @DebugSwitch int = 0 declare @XUser nvarchar(64) = object_name2(@@procid) declare @Xdate datetime = getutcdate() SET XACT_ABORT OFF BEGIN TRY update QBMRelation set ParentExecuteBy = left(v.ValidParentExecuteBy, 1)3 , XDateUpdated = @Xdate , XUserUpdated = @XUser from QBMRelation r join DialogColumn cc on r.uid_ChildColumn = cc.UID_DialogColumn join DialogTable c 4on cc.UID_DialogTable = c.UID_DialogTable join DialogColumn cp on r.UID_ParentColumn = cp.UID_DialogColumn join DialogTable p on cp.UID_DialogTable = p.UID_DialogTable5 join dbo.QBM_FTRelationValidationInfo() v on v.RIType = 'Fix' and v.Parenttype = p.TableType and v.ChildType = c.TableType where v.ValidParentExecuteBy6 not like '%' + r.ParentExecuteBy + '%' and v.IsValid = 1 select @CountItems = @@ROWCOUNT if @DebugSwitch > 0 begin print 'parentexecuteby geändert' + 7str(@CountItems) end update QBMRelation set ChildExecuteBy = left(v.ValidChildExecuteBy, 1) , XDateUpdated = @Xdate , XUserUpdated = @XUser from QBMRelation8 r join DialogColumn cc on r.UID_ChildColumn = cc.UID_DialogColumn join DialogTable c on cc.UID_DialogTable = c.UID_DialogTable join DialogColumn cp on9 r.UID_ParentColumn = cp.UID_DialogColumn join DialogTable p on cp.UID_DialogTable = p.UID_DialogTable join dbo.QBM_FTRelationValidationInfo() v on v.RIType10 = 'Fix' and v.Parenttype = p.TableType and v.ChildType = c.TableType where v.ValidChildExecuteBy not like '%' + r.ChildExecuteBy + '%' and v.IsValid =11 1 select @CountItems = @@ROWCOUNT if @DebugSwitch > 0 begin print 'Childexecuteby' + str(@CountItems) end  update DialogColumn set isForeignKey = sign12(LEN(isnull(r.UID_ChildColumn, ''))) , XDateUpdated = @Xdate , XUserUpdated = @XUser from DialogColumn c left outer join QBMRelation r on r.UID_ChildColumn13 = c.UID_DialogColumn where isnull(c.isForeignKey,0) <> sign(LEN(isnull(r.UID_ChildColumn, '')))    update QBMRelation set IsMNRelation = 0  , UID_QBMRelationMN14 = null , XDateUpdated = @Xdate , XUserUpdated = @XUser where IsMNRelation = 1  or UID_QBMRelationMN > ' ' update QBMRelation set isMNRelation = 1   , 15UID_QBMRelationMN = x.MNRelationUID , XDateUpdated = @Xdate , XUserUpdated = @XUser from QBMRelation , ( select linkscr.UID_QBMRelation ,linkscr.UID_DialogTableParent16, linkscr.UID_DialogTableChild as mnTable, rechtscr.UID_DialogTableParent as mnPartnerTable , rechtscr.UID_QBMRelation as MNRelationUID , rechtscr.RelationID17 as MNRelationID from QBM_VQBMRelation linksCR join QBM_VQBMRelation rechtsCR on linksCR.UID_DialogTableChild = rechtsCR.UID_DialogTableChild and linksCR.UID_QBMRelation18 <> rechtsCR.UID_QBMRelation join ( select UID_DialogTable , count(*) as CountItems from dialogcolumn where ispkmember = 1 and UID_DialogTable in (select19 r.UID_DialogTableChild from QBM_VQBMRelation r ) group by UID_DialogTable having count(*) > 1 ) as n1 on n1.UID_DialogTable = linkscr.UID_DialogTableChild20 join ( select UID_DialogTableChild, count(*) as CountItems from QBM_VQBMRelation  group by UID_DialogTableChild having count(*) > 1 ) as n2 on n2.UID_DialogTableChild21 = linkscr.UID_DialogTableChild join ( select dcr.UID_QBMRelation from QBMRelation dcr join dialogcolumn dc on dcr.uid_childcolumn = dc.uid_dialogcolumn22 and dc.isPKMember = 1 ) as n3 on n3.UID_QBMRelation = linkscr.UID_QBMRelation join ( select dcr.UID_QBMRelation from QBMRelation dcr join dialogcolumn23 dc on dcr.uid_childcolumn = dc.uid_dialogcolumn and dc.isPKMember = 1 ) as n4 on n4.UID_QBMRelation = rechtscr.UID_QBMRelation ) as x where QBMRelation.UID_QBMRelation24 = x.UID_QBMRelation  and QBMRelation.ParentExecuteBy in ('T', 'D' , 'N'  )  and QBMRelation.IsMNRelation = 0  update DialogTable set isMNTable = 0  , 25XDateUpdated = @Xdate , XUserUpdated = @XUser from DialogTable t where t.TableType not in (  'U', 'R') and t.isMNTable = 1 and Not exists (select top 126 1 from QBMRelation r join DialogColumn c on r.UID_ChildColumn = c.UID_DialogColumn where c.UID_DialogTable = t.UID_DialogTable and r.ismnrelation = 1 27) update dialogtable set ismntable = 1 , XDateUpdated = @Xdate , XUserUpdated = @XUser  from DialogTable t where exists (select top 1 1 from QBMRelation28 r join DialogColumn c on r.UID_ChildColumn = c.UID_DialogColumn where c.UID_DialogTable = t.UID_DialogTable and r.ismnrelation = 1 )  and t.isMNTable 29= 0 and t.TableType not in ( 'U', 'R')  update DialogTable set IsMAllTable = 1 , XDateUpdated = @Xdate , XUserUpdated = @XUser  from DialogTable t join30 QBM_VMAllTables v on t.UID_DialogTable = v.UID_DialogTable where t.IsMAllTable = 0 and t.TableType not in ( 'U', 'R') update DialogTable set IsMAllTable31 = 0 , XDateUpdated = @Xdate , XUserUpdated = @XUser  from DialogTable t left outer join QBM_VMAllTables v on t.UID_DialogTable = v.UID_DialogTable where32 t.IsMAllTable = 1 and v.UID_DialogTable is null and t.TableType not in ( 'U', 'R')  update DialogColumn set IsMAllKeyMember = 1 , XDateUpdated = @Xdate33 , XUserUpdated = @XUser  from DialogColumn c join QBM_VMAllTables v on c.UID_DialogTable = v.UID_DialogTable and ( c.UID_DialogColumn = v.UID_DialogColumnFK34 or c.UID_DialogColumn = v.UID_DialogColumnObjectKey ) where c.IsMAllKeyMember = 0 update DialogColumn set IsMAllKeyMember = 0 , XDateUpdated = @Xdate 35, XUserUpdated = @XUser  from DialogColumn c left outer join QBM_VMAllTables v on c.UID_DialogTable = v.UID_DialogTable and ( c.UID_DialogColumn = v.UID_DialogColumnFK36 or c.UID_DialogColumn = v.UID_DialogColumnObjectKey ) where c.IsMAllKeyMember = 1 and v.UID_DialogTable is null    select @CountItems = 1 while @CountItems37 > 0 begin select @CountItems = 0               update QBMRelation set parentAllowUpdate = b.parentAllowUpdate, parentrestriction = b.parentrestriction38 , XDateUpdated = @Xdate , XUserUpdated = @XUser from QBMRelation, QBMRelation b where QBMRelation.UID_QBMRelationBase = b.UID_QBMRelation and (QBMRelation.parentAllowUpdate39 <> b.parentAllowUpdate or QBMRelation.parentrestriction <> b.parentrestriction ) and b.parentAllowUpdate = 0 select @CountItems = @CountItems + @@rowcount40 update QBMRelation set ChildAllowUpdate = b.ChildAllowUpdate, ChildRestriction = b.ChildRestriction , XDateUpdated = @Xdate , XUserUpdated = @XUser from41 QBMRelation, QBMRelation b where QBMRelation.UID_QBMRelationBase = b.UID_QBMRelation and ( QBMRelation.ChildAllowUpdate <> b.ChildAllowUpdate or QBMRelation.ChildRestriction42 <> b.ChildRestriction ) and b.ChildAllowUpdate = 0 select @CountItems = @CountItems + @@rowcount   update QBMRelation set parentAllowUpdate = 0 , XDateUpdated43 = @Xdate , XUserUpdated = @XUser from QBMRelation, QBMRelation b where QBMRelation.UID_QBMRelationBase = b.UID_QBMRelation and QBMRelation.parentAllowUpdate44 <> 0 and b.parentAllowUpdate = 1 and b.Parentexecuteby = 'T' select @CountItems = @CountItems + @@rowcount update QBMRelation set ChildAllowUpdate = 045 , XDateUpdated = @Xdate , XUserUpdated = @XUser from QBMRelation, QBMRelation b where QBMRelation.UID_QBMRelationBase = b.UID_QBMRelation and QBMRelation.ChildAllowUpdate46 <> 0 and b.ChildAllowUpdate = 1 and b.Childexecuteby = 'T' select @CountItems = @CountItems + @@rowcount  update QBMRelation set parentAllowUpdate = 147 , XDateUpdated = @Xdate , XUserUpdated = @XUser from QBMRelation, QBMRelation b where QBMRelation.UID_QBMRelationBase = b.UID_QBMRelation and QBMRelation.parentAllowUpdate48 <> 1 and b.parentAllowUpdate = 1 and b.Parentexecuteby = 'D' select @CountItems = @CountItems + @@rowcount update QBMRelation set ChildAllowUpdate = 149 , XDateUpdated = @Xdate , XUserUpdated = @XUser from QBMRelation, QBMRelation b where QBMRelation.UID_QBMRelationBase = b.UID_QBMRelation and QBMRelation.ChildAllowUpdate50 <> 1 and b.ChildAllowUpdate = 1 and b.Childexecuteby = 'D' select @CountItems = @CountItems + @@rowcount end   END TRY BEGIN CATCH exec QBM_PSessionErrorAdd51 default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH endLabel: end 52