Back to OIM Explorer

dbo.QBM_PGUIDReplaceLighter

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 5.474 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_FGISessionErrorRethrow source text reference
  • references source dbo.QBM_PFDA25F6EA8E748702E6D119_1 source text reference
  • references source dbo.QBM_PFDA25F6EA8E748702E6D119_2 source text reference
  • references source dbo.QBM_PGUIDReplaceLight source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • references source dbo.QBM_TUIndicator474488 source text reference

References

Referenced By

Complete Source

SQL274 lines
1CREATE PROCEDURE QBM_PGUIDReplaceLighter(2  @StartTable varchar(30),3  @StartColumn varchar(30),4  @ReplaceValues QBM_YParameterlist READONLY,5  @IncludeLob BIT = 06)7AS8BEGIN9  DECLARE @sqlcmd nvarchar(max)10  DECLARE @Debugswitch int = 011  DECLARE @IsSimpleMAll BIT = 012  DECLARE @ElementCount int13  DECLARE @ElementIndex int14  SET XACT_ABORT OFF15  BEGIN TRY16    IF @Debugswitch = 017    BEGIN18      IF EXISTS(19        SELECT20          TOP 1 o.name,21      t.name22        FROM sys.objects o23        WITH(readpast)24      JOIN sys.triggers t25        WITH(readpast)26        ON o.object_id = t.parent_id27      WHERE28        o.type IN('U') AND t.is_disabled = 0 AND o.name NOT IN('QBMGuidReplace') AND t.name != 'QBM_TUIndicator474488') OR EXISTS(29      SELECT30        p.name,31        c.name32      FROM sys.foreign_keys fk33        WITH(readpast)34      JOIN sys.objects c35        WITH(readpast)36        ON c.object_id = fk.parent_object_id37      JOIN sys.objects p38        WITH(readpast)39        ON p.object_id = fk.referenced_object_id40      WHERE41        fk.type = 'F' AND c.type IN('U') AND p.type IN('U') AND fk.is_disabled = 0)42      BEGIN43        RAISERROR('#LDS#Usage of the procedure is only allowed when ALL triggers and FK-constraints are switched off.|',44        18,45        1)46          WITH nowait47      END48    END49    CREATE TABLE #Zwischenstand(50      PK_Old varchar(38) collate database_default,51      PK_New varchar(38) collate database_default,52      XO_Old varchar(138) collate database_default,53      XO_New varchar(138) collate database_default54    )55    CREATE TABLE #FinaleStatements(56      ElementIndex int identity primary key,57      ContentFull nvarchar(max) collate database_default58    )59    IF EXISTS(60      SELECT TOP 1 161      FROM DialogTable t62    LEFT63    OUTER64      JOIN QBM_VQBMRelation r65        ON t.TableName = r.ParentTable66      WHERE67        t.IsMAllTable = 1 AND t.TableName = @StartTable AND r.UID_QBMRelation IS NULL68  )69  BEGIN70    SELECT @IsSimpleMAll = 171  END72  IF @IsSimpleMAll = 173  BEGIN74    INSERT INTO #Zwischenstand(PK_Old,75    PK_New,76    XO_Old,77    XO_New78  )79  SELECT80    r.Parameter1,81    r.Parameter2,82    CONCAT('<Key><T>',83    @StartTable,84    '</T><P>',85    r.Parameter1,86    '</P></Key>'87    ),88    CONCAT('<Key><T>',89    @StartTable,90    '</T><P>',91    r.Parameter2,92    '</P></Key>'93    )94FROM @ReplaceValues r95WHERE96  r.Parameter1 <> r.Parameter297CREATE index #Zwischenstand198  ON #Zwischenstand(99  PK_old100) include(PK_New,101XO_New102)103CREATE index #Zwischenstand2104  ON #Zwischenstand(105  XO_old106) include(XO_New107)108END109ELSE110BEGIN111  INSERT INTO #Zwischenstand(PK_Old,112  PK_New113)114SELECT115  r.Parameter1,116  r.Parameter2117FROM @ReplaceValues r118WHERE119  r.Parameter1 <> r.Parameter2120CREATE index #Zwischenstand3121  ON #Zwischenstand(122  PK_Old123) include(PK_New124)125CREATE index #Zwischenstand4126  ON #Zwischenstand(127  XO_Old128) include(XO_New129)130SELECT131  @sqlcmd = CONCAT('132	update #Zwischenstand 133		set XO_Old = x.XObjectKey134			, XO_New = replace(x.XObjectKey, z.PK_Old, z.PK_New)135		from #Zwischenstand z join '136  ,137  @StartTable,138  ' x on z.PK_Old = x.',139  @StartColumn,140  '141		'142  )143EXEC sp_executesql @sqlcmd144END145DELETE #Zwischenstand146FROM #Zwischenstand z147WHERE148  z.XO_Old IS NULL149IF NOT EXISTS(150  SELECT TOP 1 1151  FROM #Zwischenstand152)153BEGIN154  IF @Debugswitch > 0155  BEGIN156    print 'nix zu tun'157  END158  GOTO endLabel159END160IF @IncludeLob = 1161BEGIN162  INSERT INTO #FinaleStatements(ContentFull163)164SELECT x.StatementCode165FROM(166SELECT167  replace(replace(replace(replace(replace(v.StamentPattern,168  '#tabelle#',169  s.TableName),170  '#spalte#',171  s.ColumnName),172  '#alt#',173  u.Parameter1),174  '#neu#',175  u.Parameter2),176  '#otherPK#',177  s.OtherPK)178  AS179  StatementCode,180  s.TableName,181  s.ColumnName182FROM @ReplaceValues u CROSS183JOIN(184SELECT185  t.TableName AS TableName, c.ColumnName AS ColumnName, 'R' AS StatementType, '' AS OtherPK186FROM DialogColumn c187  WITH(readpast)188JOIN DialogTable t189  WITH(readpast)190  ON c.UID_DialogTable = t.UID_DialogTable191WHERE192  (c.SyntaxType = 'XML' OR c.SyntaxType LIKE 'SQL%' OR c.SyntaxType LIKE 'VB%') AND c.SchemaDataLen <> 138) s193JOIN DialogTable te194  ON te.TableName = s.TableName AND te.TableType IN('B', 'T')195JOIN INFORMATION_SCHEMA.COLUMNS ic196  WITH(readpast)197  ON ic.TABLE_NAME = s.TableName AND ic.COLUMN_NAME = s.ColumnName198JOIN(199VALUES('R', 'if exists (select top 1 1200								from #tabelle# t201								where t.#spalte# like  ''%#alt#%'' 202							)203							 begin204								update #tabelle# set #spalte# = replace(#spalte#, ''#alt#'', ''#neu#'') where #spalte# like  ''%#alt#%'' 205							 end206							'207)) AS v(StatementType, StamentPattern)208  ON s.StatementType = v.StatementType209WHERE210  u.Parameter1 <> u.Parameter2) AS x211END212IF @IsSimpleMAll = 0213BEGIN214  IF @Debugswitch > 0215  BEGIN216    print 'Aufruf altes Verfahren'217  END218  EXEC QBM_PFDA25F6EA8E748702E6D119_1 @StartTable,219    @StartColumn220  IF @Debugswitch > 0221  BEGIN222    SELECT223      CONCAT(f.ContentFull,224      nchar(13),225      nchar(10),226      '------------@IsSimpleMAll = 0------------------------------')227    FROM #FinaleStatements f228  END229END230ELSE231BEGIN232  IF @Debugswitch > 0233  BEGIN234    print 'Aufruf neues Verfahren'235  END236  EXEC QBM_PFDA25F6EA8E748702E6D119_2 @StartTable237  IF @Debugswitch > 0238  BEGIN239    SELECT240      CONCAT(f.ContentFull,241      nchar(13),242      nchar(10),243      '---------------@IsSimpleMAll = 1 ---------------------------')244    FROM #FinaleStatements f245  END246END247SELECT @ElementCount = COUNT(*)248FROM #FinaleStatements249SELECT @ElementIndex = 1250WHILE @ElementIndex <= @ElementCount251BEGIN252  SELECT TOP 1 @sqlcmd = bu.ContentFull253  FROM #FinaleStatements bu254  WHERE255    bu.ElementIndex = @ElementIndex256  IF @Debugswitch > 0257  BEGIN258    print @sqlcmd259  END260  EXEC sp_executesql @sqlcmd261  SELECT @ElementIndex += 1262END263END TRY264BEGIN CATCH265  EXEC QBM_PSessionErrorAdd DEFAULT266  DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()267  RAISERROR(@Rethrow,268  18,269  1)270    WITH NOWAIT271END CATCH272endLabel: truncate TABLE #Zwischenstand truncate TABLE #FinaleStatements273RETURN274END
Open raw exported source
SQL ยท Raw47 lines
1    create   procedure QBM_PGUIDReplaceLighter (@StartTable varchar(30) , @StartColumn varchar(30) , @ReplaceValues QBM_YParameterlist readonly 2  , @IncludeLob bit = 0 ) as begin declare @sqlcmd nvarchar(max) declare @Debugswitch int = 0 declare @IsSimpleMAll bit = 0 declare @ElementCount int declare3 @ElementIndex int SET XACT_ABORT OFF BEGIN TRY if @Debugswitch = 0 begin  if exists (select top 1 o.name, t.name from sys.objects o with (readpast) join4 sys.triggers t with (readpast) on o.object_id = t.parent_id where o.type in( 'U' ) and t.is_disabled = 0 and o.name not in ('QBMGuidReplace') and t.name5 != 'QBM_TUIndicator474488' ) or exists (select p.name, c.name from sys.foreign_keys fk with (readpast) join sys.objects c with (readpast) on c.object_id6 = fk.parent_object_id join sys.objects p with (readpast) on p.object_id = fk.referenced_object_id where fk.type = 'F' and c.type in( 'U' ) and p.type 7in( 'U' ) and fk.is_disabled = 0 ) begin raiserror('#LDS#Usage of the procedure is only allowed when ALL triggers and FK-constraints are switched off.|'8, 18, 1) with nowait end end create table #Zwischenstand(PK_Old varchar(38) collate database_default , PK_New varchar(38) collate database_default , XO_Old9 varchar(138) collate database_default , XO_New varchar(138) collate database_default )    create table #FinaleStatements(ElementIndex int identity primary10 key , ContentFull nvarchar(max) collate database_default ) if exists (select top 1 1 from DialogTable t left outer join QBM_VQBMRelation r on t.TableName11 = r.ParentTable where t.IsMAllTable = 1 and t.TableName = @StartTable and r.UID_QBMRelation is null ) begin select @IsSimpleMAll = 1 end if @IsSimpleMAll12 = 1 begin insert into #Zwischenstand(PK_Old, PK_New, XO_Old, XO_New) select r.Parameter1, r.Parameter2 , CONCAT( '<Key><T>', @StartTable , '</T><P>', 13r.Parameter1 , '</P></Key>' ) , CONCAT( '<Key><T>', @StartTable , '</T><P>', r.Parameter2 , '</P></Key>' ) from @ReplaceValues r where r.Parameter1 <> 14r.Parameter2 create index #Zwischenstand1 on #Zwischenstand(PK_old) include (PK_New, XO_New) create index #Zwischenstand2 on #Zwischenstand(XO_old) include15 (XO_New) end else begin insert into #Zwischenstand(PK_Old, PK_New) select r.Parameter1, r.Parameter2 from @ReplaceValues r where r.Parameter1 <> r.Parameter216 create index #Zwischenstand3 on #Zwischenstand(PK_Old) include (PK_New) create index #Zwischenstand4 on #Zwischenstand(XO_Old) include (XO_New) select17 @sqlcmd = concat('18	update #Zwischenstand 19		set XO_Old = x.XObjectKey20			, XO_New = replace(x.XObjectKey, z.PK_Old, z.PK_New)21		from #Zwischenstand z join '22, @StartTable , ' x on z.PK_Old = x.', @StartColumn , '23		' ) exec sp_executesql @sqlcmd end   delete #Zwischenstand from #Zwischenstand z where z.XO_Old24 is null if not exists (select top 1 1 from #Zwischenstand ) begin if @Debugswitch > 0 begin print 'nix zu tun' end goto endLabel end  if @IncludeLob =25 1 begin insert into #FinaleStatements(ContentFull) select x.StatementCode  from ( select replace(replace(replace(replace(replace(v.StamentPattern , '#tabelle#'26, s.TableName) , '#spalte#', s.ColumnName) , '#alt#', u.Parameter1) , '#neu#', u.Parameter2) , '#otherPK#', s.OtherPK) as StatementCode , s.TableName, 27s.ColumnName from @ReplaceValues u cross join ( select t.TableName as TableName, c.ColumnName as ColumnName , 'R' as StatementType, '' as OtherPK from 28DialogColumn c with (readpast) join DialogTable t with (readpast) on c.UID_DialogTable = t.UID_DialogTable where (c.SyntaxType = 'XML' or c.SyntaxType 29like 'SQL%' or c.SyntaxType like 'VB%' ) and c.SchemaDataLen <> 138 ) s join DialogTable te on te.TableName = s.TableName and te.TableType in ('B', 'T'30)  join INFORMATION_SCHEMA.COLUMNS ic with (readpast) on ic.TABLE_NAME = s.TableName and ic.COLUMN_NAME = s.ColumnName join ( values ('R', 'if exists (select top 1 131								from #tabelle# t32								where t.#spalte# like  ''%#alt#%'' 33							)34							 begin35								update #tabelle# set #spalte# = replace(#spalte#, ''#alt#'', ''#neu#'') where #spalte# like  ''%#alt#%'' 36							 end37							'38) ) as v (StatementType, StamentPattern) on s.StatementType = v.StatementType where u.Parameter1 <> u.Parameter2 ) as x end   if @IsSimpleMAll = 0  begin39 if @Debugswitch > 0 begin print 'Aufruf altes Verfahren' end  exec QBM_PFDA25F6EA8E748702E6D119_1 @StartTable , @StartColumn if @Debugswitch > 0 begin40 select concat(f.ContentFull, nchar(13), nchar(10), '------------@IsSimpleMAll = 0------------------------------') from #FinaleStatements f end end else41 begin if @Debugswitch > 0 begin print 'Aufruf neues Verfahren' end  exec QBM_PFDA25F6EA8E748702E6D119_2 @StartTable  if @Debugswitch > 0 begin select 42concat(f.ContentFull, nchar(13), nchar(10), '---------------@IsSimpleMAll = 1 ---------------------------') from #FinaleStatements f end end select @ElementCount43 = COUNT(*) from #FinaleStatements select @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top 1 @sqlcmd = bu.ContentFull from #FinaleStatements44 bu where bu.ElementIndex = @ElementIndex if @Debugswitch > 0 begin print @sqlcmd end exec sp_executesql @sqlcmd select @ElementIndex += 1 end  END TRY45 BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT46 END CATCH endLabel: truncate table #Zwischenstand truncate table #FinaleStatements return end 47