Back to OIM Explorer

dbo.QBM_PSplittedElementProcess

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 3.700 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_FCVStringToGUID source text reference

References

dbo.QBM_FCVStringToGUID

Referenced By

dbo.QBM_FSQTriggerSplittedLookup

Complete Source

1CREATE PROCEDURE QBM_PSplittedElementProcess(2  @Sources QBM_YParameterList READONLY,3  @SplittedElementType varchar(16)4)5AS6BEGIN7  DECLARE @IstInsert int = 08  DECLARE @IstDelete int = 09  DECLARE @IstUpdate int = 010  DECLARE @DebugSwitch int = 011  DECLARE @soll QBM_YSplittedElementHelper12  DECLARE @ist QBM_YSplittedElementHelper13  IF @DebugSwitch > 014  BEGIN15    print 'gelieferte @Sources für ElementType ' + @SplittedElementType16    SELECT *17    FROM @Sources18  END19  IF NOT EXISTS(20    SELECT TOP 1 121    FROM @Sources)22  BEGIN23    IF @DebugSwitch > 024    BEGIN25      print 'nix geliefert, return'26    END27    RETURN28  END29  INSERT INTO @soll(UID_QBMSplittedLookup,30  UID_DialogColumn,31  ObjectKeyOwner,32  SplittedElement,33  UID_Person)34  SELECT35    dbo.QBM_FCVStringToGUID('',36    CONCAT(x.UID_DialogColumn, x.ObjectKeyOwner, x.SplittedElement)),37    x.UID_DialogColumn,38    x.ObjectKeyOwner,39    isnull(x.SplittedElement,40    ''),41    x.UID_Person42  FROM(43  SELECT44    s.Parameter2 AS UID_DialogColumn,45    s.Parameter3 AS ObjectKeyOwner,46    s.ContentShort AS SplittedElement,47    s.Parameter1 AS UID_Person48  FROM @Sources s49  WHERE50    s.HasContentFull = 051  UNION52  SELECT53    s.Parameter2 AS UID_DialogColumn,54    s.Parameter3 AS ObjectKeyOwner,55    p.SplittedElement AS SplittedElement,56    s.Parameter1 AS UID_Person57  FROM @Sources s58  OUTER apply(59  SELECT CASE @SplittedElementType60  WHEN 'Email' THEN61  replace(y.SplittedElement, 'smtp:', '')62  ELSE y.SplittedElement63  END AS SplittedElement64  FROM(65  SELECT trim(x.value) AS SplittedElement66  FROM string_split(s.ContentFull, nchar(7)) x) AS y) AS p67  WHERE68    s.HasContentFull = 1) AS x69  IF @DebugSwitch > 070  BEGIN71    print 'Erstbefüllung soll'72    SELECT *73    FROM @soll74  END75  INSERT INTO @ist(UID_QBMSplittedLookup,76  UID_DialogColumn,77  ObjectKeyOwner,78  SplittedElement,79  UID_Person)80  SELECT81    DISTINCT t.UID_QBMSplittedLookup,82    t.UID_DialogColumn,83    t.ObjectKeyOwner,84    '',85    t.UID_Person86  FROM QBMSplittedLookup t87  JOIN @soll s88    ON t.ObjectKeyOwner = s.ObjectKeyOwner AND t.UID_DialogColumn = s.UID_DialogColumn89  IF @DebugSwitch > 090  BEGIN91    print 'Erstbefüllung ist'92    SELECT *93    FROM @ist94  END95  UPDATE @soll96  SET IsToInsert = 197  FROM @soll s98  LEFT99  OUTER100  JOIN @ist i101    ON s.UID_QBMSplittedLookup = i.UID_QBMSplittedLookup102  WHERE103    i.UID_QBMSplittedLookup IS NULL104  SELECT @IstInsert = @@ROWCOUNT105  IF @DebugSwitch > 0106  BEGIN107    print 'Soll nach Bewertung Neuaufnahme'108    SELECT *109    FROM @soll110  END111  DELETE @soll112  FROM @soll s113  WHERE114    s.SplittedElement = ''115  IF @DebugSwitch > 0116  BEGIN117    print 'Soll nach Löschung leere'118    SELECT *119    FROM @soll120  END121  UPDATE @ist122  SET isToDelete = 1123  FROM @ist i124  LEFT125  OUTER126  JOIN @soll s127    ON i.UID_QBMSplittedLookup = s.UID_QBMSplittedLookup128  WHERE129    s.UID_QBMSplittedLookup IS NULL130  SELECT @IstDelete = @@ROWCOUNT131  UPDATE @soll132  SET IsToUpdate = 1133  FROM @ist i134  JOIN @soll s135    ON i.UID_QBMSplittedLookup = s.UID_QBMSplittedLookup136  WHERE137    isnull(i.UID_Person,138  '') <> isnull(s.UID_Person,139  '')140  SELECT @IstUpdate = @@ROWCOUNT141  IF @DebugSwitch > 0142  BEGIN143    print 'Nach Bewertung soll'144    SELECT *145    FROM @soll print 'Nach Bewertung ist'146    SELECT *147    FROM @ist148  END149  IF @IstDelete > 0150  BEGIN151    DELETE QBMSplittedLookup152    FROM QBMSplittedLookup d153    JOIN @ist s154      ON d.UID_QBMSplittedLookup = s.UID_QBMSplittedLookup155    WHERE156      s.isToDelete = 1157  END158  IF @IstInsert > 0159  BEGIN160    INSERT INTO QBMSplittedLookup(UID_QBMSplittedLookup,161    UID_DialogColumn,162    ObjectKeyOwner,163    SplittedElement,164    UID_Person,165    SplittedElementType)166    SELECT167      s.UID_QBMSplittedLookup,168      s.UID_DialogColumn,169      s.ObjectKeyOwner,170      s.SplittedElement,171      s.UID_Person,172      @SplittedElementType173    FROM @soll s174    WHERE175      s.IsToInsert = 1176  END177  IF @IstUpdate > 0178  BEGIN179    UPDATE QBMSplittedLookup180    SET UID_Person = s.UID_Person181    FROM QBMSplittedLookup i182    JOIN @soll s183      ON i.UID_QBMSplittedLookup = s.UID_QBMSplittedLookup184    WHERE185      s.IsToUpdate = 1186  END187  endLabel:188  RETURN189END

Open raw exported source

1        create   procedure QBM_PSplittedElementProcess (@Sources QBM_YParameterList readonly , @SplittedElementType varchar(16) ) as begin declare2 @IstInsert int = 0 declare @IstDelete int = 0 declare @IstUpdate int = 0 declare @DebugSwitch int = 0 declare @soll QBM_YSplittedElementHelper declare3 @ist QBM_YSplittedElementHelper if @DebugSwitch > 0 begin print 'gelieferte @Sources für ElementType ' + @SplittedElementType select * from @Sources  4   end if not exists (select top 1 1 from @Sources ) begin if @DebugSwitch > 0 begin print 'nix geliefert, return' end return end insert into @soll(UID_QBMSplittedLookup5, UID_DialogColumn, ObjectKeyOwner, SplittedElement, UID_Person) select     dbo.QBM_FCVStringToGUID('', concat(x.UID_DialogColumn , x.ObjectKeyOwner , 6x.SplittedElement ))    , x.UID_DialogColumn, x.ObjectKeyOwner, isnull(x.SplittedElement, ''), x.UID_Person from ( select s.Parameter2 as UID_DialogColumn7 , s.Parameter3 as ObjectKeyOwner, s.ContentShort as SplittedElement , s.Parameter1 as UID_Person from @Sources s where s.HasContentFull = 0  union  select8 s.Parameter2 as UID_DialogColumn, s.Parameter3 as ObjectKeyOwner, p.SplittedElement as SplittedElement, s.Parameter1 as UID_Person from @Sources s outer9 apply (select case @SplittedElementType when 'Email' then replace(y.SplittedElement, 'smtp:', '') else y.SplittedElement end as SplittedElement from (10 select trim(x.value) as SplittedElement from string_split (s.ContentFull, nchar(7)) x      ) as y ) as p where s.HasContentFull = 1  ) as x if @DebugSwitch11 > 0 begin print 'Erstbefüllung soll' select * from @soll end insert into @ist(UID_QBMSplittedLookup, UID_DialogColumn, ObjectKeyOwner, SplittedElement12, UID_Person ) select distinct t.UID_QBMSplittedLookup, t.UID_DialogColumn, t.ObjectKeyOwner, '', t.UID_Person  from QBMSplittedLookup t join @soll s on13 t.ObjectKeyOwner = s.ObjectKeyOwner and t.UID_DialogColumn = s.UID_DialogColumn if @DebugSwitch > 0 begin print 'Erstbefüllung ist' select * from @ist14 end update @soll set IsToInsert = 1 from @soll s left outer join @ist i on s.UID_QBMSplittedLookup = i.UID_QBMSplittedLookup where i.UID_QBMSplittedLookup15 is null select @IstInsert = @@ROWCOUNT if @DebugSwitch > 0 begin print 'Soll nach Bewertung Neuaufnahme' select * from @soll end delete  @soll from @soll16 s where s.SplittedElement = ''  if @DebugSwitch > 0 begin print 'Soll nach Löschung leere' select * from @soll end update @ist set isToDelete = 1 from17 @ist i left outer join @soll s on i.UID_QBMSplittedLookup = s.UID_QBMSplittedLookup where s.UID_QBMSplittedLookup is null select @IstDelete = @@ROWCOUNT18 update @soll set IsToUpdate = 1 from @ist i join @soll s on i.UID_QBMSplittedLookup = s.UID_QBMSplittedLookup where isnull(i.UID_Person, '') <> isnull19(s.UID_Person, '') select @IstUpdate = @@ROWCOUNT if @DebugSwitch > 0 begin print 'Nach Bewertung soll' select * from @soll print 'Nach Bewertung ist' 20select * from @ist end if @IstDelete > 0 begin delete QBMSplittedLookup from QBMSplittedLookup d join @ist s on d.UID_QBMSplittedLookup = s.UID_QBMSplittedLookup21 where s.isToDelete = 1 end if @IstInsert > 0 begin insert into QBMSplittedLookup( UID_QBMSplittedLookup , UID_DialogColumn , ObjectKeyOwner , SplittedElement22 , UID_Person , SplittedElementType  ) select s.UID_QBMSplittedLookup , s.UID_DialogColumn , s.ObjectKeyOwner , s.SplittedElement , s.UID_Person , @SplittedElementType23  from @soll s where s.IsToInsert = 1 end if @IstUpdate > 0 begin update QBMSplittedLookup set UID_Person = s.UID_Person from QBMSplittedLookup i join 24@soll s on i.UID_QBMSplittedLookup = s.UID_QBMSplittedLookup where s.IsToUpdate = 1 end endLabel: return end 25