dbo.QBM_PGUIDReplaceLighter

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 declare
3 @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) join
4 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.name
5 != '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_id
6 = 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_Old
9 varchar(138) collate database_default , XO_New varchar(138) collate database_default )    create table #FinaleStatements(ElementIndex int identity primary
10 key , ContentFull nvarchar(max) collate database_default ) if exists (select top 1 1 from DialogTable t left outer join QBM_VQBMRelation r on t.TableName
11 = r.ParentTable where t.IsMAllTable = 1 and t.TableName = @StartTable and r.UID_QBMRelation is null ) begin select @IsSimpleMAll = 1 end if @IsSimpleMAll
12 = 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) include
15 (XO_New) end else begin insert into #Zwischenstand(PK_Old, PK_New) select r.Parameter1, r.Parameter2 from @ReplaceValues r where r.Parameter1 <> r.Parameter2
16 create index #Zwischenstand3 on #Zwischenstand(PK_Old) include (PK_New) create index #Zwischenstand4 on #Zwischenstand(XO_Old) include (XO_New) select
17 @sqlcmd = concat('
18	update #Zwischenstand 
19		set XO_Old = x.XObjectKey
20			, 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_Old
24 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 1
31								from #tabelle# t
32								where t.#spalte# like  ''%#alt#%'' 
33							)
34							 begin
35								update #tabelle# set #spalte# = replace(#spalte#, ''#alt#'', ''#neu#'') where #spalte# like  ''%#alt#%'' 
36							 end
37							'
38) ) as v (StatementType, StamentPattern) on s.StatementType = v.StatementType where u.Parameter1 <> u.Parameter2 ) as x end   if @IsSimpleMAll = 0  begin
39 if @Debugswitch > 0 begin print 'Aufruf altes Verfahren' end  exec QBM_PFDA25F6EA8E748702E6D119_1 @StartTable , @StartColumn if @Debugswitch > 0 begin
40 select concat(f.ContentFull, nchar(13), nchar(10), '------------@IsSimpleMAll = 0------------------------------') from #FinaleStatements f end end else
41 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 @ElementCount
43 = COUNT(*) from #FinaleStatements select @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top 1 @sqlcmd = bu.ContentFull from #FinaleStatements
44 bu where bu.ElementIndex = @ElementIndex if @Debugswitch > 0 begin print @sqlcmd end exec sp_executesql @sqlcmd select @ElementIndex += 1 end  END TRY
45 BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT
46 END CATCH endLabel: truncate table #Zwischenstand truncate table #FinaleStatements return end 
47