Back to OIM Explorer

dbo.QBM_PRelationshipDefineManual

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 3.839 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_FCVElementToObjectKey1 source text reference

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL148 lines
1CREATE PROCEDURE QBM_PRelationshipDefineManual(2  @UID_QBMRelation varchar(38),3  @UID_QBMRelationBase varchar(38),4  @ParentTable varchar(30),5  @ParentColumn varchar(30),6  @ChildTable varchar(30),7  @ChildColumn varchar(30)8)9AS10BEGIN11  DECLARE @UID_DialogTableParent varchar(38)12  DECLARE @UID_DialogTableChild varchar(38)13  DECLARE @UID_ChildColumn varchar(38)14  DECLARE @UID_ParentColumn varchar(38)15  DECLARE @Xdate datetime = getutcdate()16  DECLARE @XUser nvarchar(64) = object_name(@@procid)17  DECLARE @DebugSwitch int = 018  DECLARE @CountItems int19  SELECT TOP 1 @UID_DialogTableParent = t.UID_DialogTable20  FROM DialogTable t21  WHERE22    t.TableName = @ParentTable23  SELECT TOP 1 @UID_DialogTableChild = t.UID_DialogTable24  FROM DialogTable t25  WHERE26    t.TableName = @ChildTable27  SELECT TOP 1 @UID_ParentColumn = c.UID_DialogColumn28  FROM DialogColumn c29  WHERE30    c.UID_DialogTable = @UID_DialogTableParent AND c.ColumnName = @ParentColumn31  SELECT TOP 1 @UID_ChildColumn = c.UID_DialogColumn32  FROM DialogColumn c33  WHERE34    c.UID_DialogTable = @UID_DialogTableChild AND c.ColumnName = @ChildColumn35  IF NOT EXISTS(36    SELECT TOP 1 137    FROM QBMRelation r38    WHERE39      r.UID_QBMRelation = @UID_QBMRelation)40  BEGIN41    INSERT INTO QBMRelation(RelationID,42    UID_QBMRelation,43    UID_ParentColumn,44    ParentRestriction,45    ParentExecuteBy,46    ParentAllowUpdate,47    UID_ChildColumn,48    ChildRestriction,49    ChildExecuteBy,50    ChildAllowUpdate,51    XDateInserted,52    XDateUpdated,53    XUserInserted,54    XUserUpdated,55    XObjectKey,56    UID_QBMRelationBase,57    XTouched)58    SELECT59      @UID_QBMRelation,60      @UID_QBMRelation,61      @UID_ParentColumn,62      'DC',63      'T',64      0,65      @UID_ChildColumn,66      'IR',67      'T',68      0,69      @Xdate,70      @Xdate,71      @XUser,72      @XUser,73      dbo.QBM_FCVElementToObjectKey1('QBMRelation',74      'UID_QBMRelation',75      @UID_QBMRelation),76      @UID_QBMRelationBase,77      'I'78  END79  ELSE80  BEGIN81    UPDATE QBMRelation82    SET ParentRestriction = 'DC',83    ParentExecuteBy = 'T',84    ParentAllowUpdate = 0,85    ChildRestriction = 'IR',86    ChildExecuteBy = 'T',87    ChildAllowUpdate = 0,88    UID_ChildColumn = @UID_ChildColumn,89    UID_ParentColumn = @UID_ParentColumn,90    UID_QBMRelationBase = @UID_QBMRelationBase,91    XTouched = 'U',92    XObjectKey = dbo.QBM_FCVElementToObjectKey1('QBMRelation',93    'UID_QBMRelation',94    @UID_QBMRelation),95    XDateUpdated = @Xdate,96    XUserUpdated = @XUser97    WHERE98      UID_QBMRelation = @UID_QBMRelation99  END100  UPDATE Qbmrelation101  SET parentexecuteby =102  LEFT(v.ValidParentExecuteBy,103  1),104  XDateUpdated = @Xdate,105  XUserUpdated = @XUser106  FROM qbmrelation r107  JOIN dialogcolumn cc108    ON r.uid_ChildColumn = cc.uid_dialogcolumn109  JOIN dialogtable c110    ON cc.uid_dialogtable = c.uid_dialogtable111  JOIN dialogcolumn cp112    ON r.uid_parentColumn = cp.uid_dialogcolumn113  JOIN dialogtable p114    ON cp.uid_dialogtable = p.uid_dialogtable115  JOIN dbo.QBM_FTRelationValidationInfo() v116    ON v.RIType = 'Fix' AND v.Parenttype = p.TableType AND v.ChildType = c.TableType117  WHERE118    v.ValidParentExecuteBy NOT LIKE '%' + r.ParentExecuteBy + '%' AND v.IsValid = 1 AND r.uid_QBMRelation = @UID_QBMRelation119  SELECT @CountItems = @@ROWCOUNT120  IF @DebugSwitch > 0121  BEGIN122    print 'parentexecuteby geändert' + str(@CountItems)123  END124  UPDATE qbmrelation125  SET Childexecuteby =126  LEFT(v.ValidChildExecuteBy,127  1),128  XDateUpdated = @Xdate,129  XUserUpdated = @XUser130  FROM qbmrelation r131  JOIN dialogcolumn cc132    ON r.uid_ChildColumn = cc.uid_dialogcolumn133  JOIN dialogtable c134    ON cc.uid_dialogtable = c.uid_dialogtable135  JOIN dialogcolumn cp136    ON r.uid_parentColumn = cp.uid_dialogcolumn137  JOIN dialogtable p138    ON cp.uid_dialogtable = p.uid_dialogtable139  JOIN dbo.QBM_FTRelationValidationInfo() v140    ON v.RIType = 'Fix' AND v.Parenttype = p.TableType AND v.ChildType = c.TableType141  WHERE142    v.ValidChildExecuteBy NOT LIKE '%' + r.ChildExecuteBy + '%' AND v.IsValid = 1 AND r.uid_QBMRelation = @UID_QBMRelation143  SELECT @CountItems = @@ROWCOUNT144  IF @DebugSwitch > 0145  BEGIN146    print 'Childexecuteby' + str(@CountItems)147  END148END
Open raw exported source
SQL · Raw26 lines
1 create   procedure QBM_PRelationshipDefineManual( @UID_QBMRelation varchar(38) , @UID_QBMRelationBase varchar(38) , @ParentTable varchar(30)  ,2 @ParentColumn varchar(30)  , @ChildTable varchar(30)  , @ChildColumn varchar(30)  ) as begin  declare @UID_DialogTableParent varchar(38) declare @UID_DialogTableChild3 varchar(38) declare @UID_ChildColumn varchar(38) declare @UID_ParentColumn varchar(38) declare @Xdate datetime = getutcdate() declare @XUser nvarchar(644) = object_name(@@procid) declare @DebugSwitch int = 0 declare @CountItems int select top 1 @UID_DialogTableParent = t.UID_DialogTable from DialogTable5 t where t.TableName = @ParentTable select top 1 @UID_DialogTableChild = t.UID_DialogTable from DialogTable t where t.TableName = @ChildTable select top6 1 @UID_ParentColumn = c.UID_DialogColumn from DialogColumn c where c.UID_DialogTable = @UID_DialogTableParent and c.ColumnName = @ParentColumn select 7top 1 @UID_ChildColumn = c.UID_DialogColumn from DialogColumn c where c.UID_DialogTable = @UID_DialogTableChild and c.ColumnName = @ChildColumn if not 8exists (select top 1 1 from QBMRelation r where r.UID_QBMRelation = @UID_QBMRelation ) begin insert into QBMRelation ( RelationID , UID_QBMRelation    9, UID_ParentColumn , ParentRestriction , ParentExecuteBy , ParentAllowUpdate    , UID_ChildColumn , ChildRestriction , ChildExecuteBy , ChildAllowUpdate10 , XDateInserted, XDateUpdated, XUserInserted, XUserUpdated, XObjectKey     , UID_QBMRelationBase , XTouched ) select @UID_QBMRelation  , @UID_QBMRelation11     , @UID_ParentColumn  , 'DC'  , 'T'  , 0     , @UID_ChildColumn  , 'IR'  , 'T'  , 0  , @Xdate, @Xdate, @XUser, @XUser , dbo.QBM_FCVElementToObjectKey112('QBMRelation', 'UID_QBMRelation', @UID_QBMRelation)     , @UID_QBMRelationBase  , 'I'  end else begin update QBMRelation set     ParentRestriction = 'DC'13 , ParentExecuteBy = 'T' , ParentAllowUpdate = 0 , ChildRestriction = 'IR'  , ChildExecuteBy = 'T'  , ChildAllowUpdate = 0   , UID_ChildColumn = @UID_ChildColumn14  , UID_ParentColumn = @UID_ParentColumn , UID_QBMRelationBase = @UID_QBMRelationBase , XTouched = 'U' , XObjectKey = dbo.QBM_FCVElementToObjectKey1('QBMRelation'15, 'UID_QBMRelation', @UID_QBMRelation) , XDateUpdated = @Xdate , XUserUpdated = @XUser where UID_QBMRelation = @UID_QBMRelation end update Qbmrelation 16set parentexecuteby = left(v.ValidParentExecuteBy, 1) , XDateUpdated = @Xdate , XUserUpdated = @XUser from qbmrelation r join dialogcolumn cc on r.uid_ChildColumn17 = cc.uid_dialogcolumn join dialogtable c on cc.uid_dialogtable = c.uid_dialogtable join dialogcolumn cp on r.uid_parentColumn = cp.uid_dialogcolumn join18 dialogtable p on cp.uid_dialogtable = p.uid_dialogtable join dbo.QBM_FTRelationValidationInfo() v on v.RIType = 'Fix' and v.Parenttype = p.TableType and19 v.ChildType = c.TableType where v.ValidParentExecuteBy not like '%' + r.ParentExecuteBy + '%' and v.IsValid = 1 and r.uid_QBMRelation = @UID_QBMRelation20 select @CountItems = @@ROWCOUNT if @DebugSwitch > 0 begin print 'parentexecuteby geändert' + str(@CountItems) end update qbmrelation set Childexecuteby21 = left(v.ValidChildExecuteBy, 1) , XDateUpdated = @Xdate , XUserUpdated = @XUser from qbmrelation r join dialogcolumn cc on r.uid_ChildColumn = cc.uid_dialogcolumn22 join dialogtable c on cc.uid_dialogtable = c.uid_dialogtable join dialogcolumn cp on r.uid_parentColumn = cp.uid_dialogcolumn join dialogtable p on cp.uid_dialogtable23 = p.uid_dialogtable join dbo.QBM_FTRelationValidationInfo() v on v.RIType = 'Fix' and v.Parenttype = p.TableType and v.ChildType = c.TableType where v.ValidChildExecuteBy24 not like '%' + r.ChildExecuteBy + '%' and v.IsValid = 1 and r.uid_QBMRelation = @UID_QBMRelation select @CountItems = @@ROWCOUNT if @DebugSwitch > 0 begin25 print 'Childexecuteby' + str(@CountItems) end end 26