dbo.QBM_PGUIDReplaceLight_Multi

1   create   procedure QBM_PGUIDReplaceLight_Multi (@StartElements QBM_YParameterlist readonly   , @ReplaceValues QBM_YParameterlist readonly   ,
2 @IncludeLob bit = 1 ) as begin declare @TableName varchar(30) declare @ColumnName varchar(30) declare @sqlcmd nvarchar(max) declare @Debugswitch int =
3 0 declare @Statements QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int declare @UID_StatementCode varchar(38) SET XACT_ABORT OFF 
4BEGIN TRY if @Debugswitch = 0 begin  if exists (select top 1 1 from sys.objects o with (readpast) join sys.triggers t with (readpast) on o.object_id = 
5t.parent_id where o.type in( 'U' ) and t.is_disabled = 0 and o.name not in ('QBMGuidReplace') and t.name != 'QBM_TUIndicator474488' ) or exists (select
6 top 1 1 from sys.foreign_keys fk with (readpast) join sys.objects c with (readpast) on c.object_id = fk.parent_object_id join sys.objects p with (readpast
7) on p.object_id = fk.referenced_object_id where fk.type = 'F' and c.type in( 'U' ) and p.type in( '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 insert into @Statements(ContentFull, Ident1, Ident2, UID1) select x.StatementCode, x.TableName, x.ColumnName, dbo.QBM_FCVStringToGUID
9('', x.StatementCode) from ( select replace(replace(replace(replace(replace(replace(v.StamentPattern , '#tabelle#', s.TableName) , '#spalte#', s.ColumnName
10) , '#alt#', u.Parameter1) , '#neu#', u.Parameter2) , '#otherPK#', s.OtherPK) , '#xwerte#', case when dbo.QBM_FGIColumnExistsInSchema(s.TableName, 'XDateUpdated'
11) = 1 and s.ColumnName not like 'X[d,u][a,s][t,e][e,r]updated' then ', XDateUpdated = getutcdate(), XUserUpdated =  ''' + object_name(@@procid) + '''' 
12else '' end ) as StatementCode , s.TableName, s.ColumnName from @ReplaceValues u cross join (  select r.ChildTable as TableName, r.ChildColumn as ColumnName
13, 'U' as StatementType, '' as OtherPK from QBM_VQBMRelation r join DialogColumn c with (readpast) on r.UID_ChildColumn = c.UID_DialogColumn join @StartElements
14 st on st.Parameter1 = r.ParentTable where c.IsPKMember = 0 union select r.ChildTable as TableName, r.ChildColumn as ColumnName, 'UE' as StatementType,
15 ra.ChildColumn as OtherPK from QBM_VQBMRelation r join DialogColumn c with (readpast) on r.UID_ChildColumn = c.UID_DialogColumn join QBM_VQBMRelation 
16ra on r.UID_DialogTableChild = ra.UID_DialogTableChild and ra.UID_QBMRelation <> r.UID_QBMRelation join @StartElements st on st.Parameter1 = r.ParentTable
17 where c.IsPKMember = 1 union  select r.ChildTable, c.ColumnName, 'RE', '' as OtherPK from QBM_VQBMRelation r join Dialogcolumn c with (readpast) on r.UID_DialogTableChild
18 = c.UID_DialogTable and c.ColumnName = 'XObjectKey'  join DialogTable t with (readpast) on r.UID_DialogTableChild = t.UID_DialogTable and t.isMNTable 
19= 1 join @StartElements st on st.Parameter1 = r.ParentTable union  select st.Parameter1 as StartTable, st.Parameter2 as StartColumn, 'UE1', '' as OtherPK
20  from @StartElements st union select st.Parameter1, 'XObjectKey', 'RE', '' as OtherPK from @StartElements st union select 'QBMTaggedChange', 'ObjectKey'
21, 'R', '' as OtherPK union select 'QBMBufferConfig', 'ObjectKeyOfRow', 'R', '' as OtherPK union select 'DialogWatchOperation', 'ObjectKeyOfRow', 'R', ''
22 as OtherPK union select 'DialogTaggedItem', 'ObjectKey', 'R', '' as OtherPK union  select tr.TableName, c.ColumnName, 'R', '' as OtherPK from DialogValidDynamicRef
23 r with (readpast) join DialogTable t with (readpast) on r.UID_DialogTableReference = t.UID_DialogTable join DialogColumn c with (readpast) on r.UID_DialogColumn
24 = c.UID_DialogColumn join DialogTable tr with (readpast) on c.UID_DialogTable = tr.UID_DialogTable join @StartElements st on st.Parameter1 = t.TableName
25  union select t.TableName, c.ColumnName, 'R', '' as OtherPK from DialogColumn c with (readpast) join DialogTable t with (readpast) on c.UID_DialogTable
26 = t.UID_DialogTable left outer join DialogValidDynamicRef r with (readpast) on r.UID_DialogColumn = c.UID_DialogColumn where c.IsDynamicFK = 1 and r.UID_DialogColumn
27 is null  union select t.TableName, c.ColumnName, 'R', '' as OtherPK from DialogColumn c with (readpast) join DialogTable t with (readpast) on c.UID_DialogTable
28 = t.UID_DialogTable where (c.SyntaxType = 'XML' or c.SyntaxType like 'SQL%' or c.SyntaxType like 'VB%' ) and c.SchemaDataLen <> 138 and @IncludeLob = 
291 ) s join DialogTable te with (readpast) on te.TableName = s.TableName and te.TableType in ('B', 'T')   join INFORMATION_SCHEMA.COLUMNS ic with (readpast
30) on ic.TABLE_NAME = te.TableName and s.ColumnName = ic.COLUMN_NAME join sys.tables t on te.TableName = t.name   and t.type = 'U' and t.is_memory_optimized
31 = 0 and ic.DATA_TYPE like '%char%' join ( values ('U', 'update #tabelle# set #spalte# = ''#neu#'' #xwerte# where #spalte# = ''#alt#''') , ('UE', 'update #tabelle# 
32										set #spalte# = ''#neu#'' #xwerte#
33									from #tabelle# t
34									where t.#spalte# = ''#alt#''
35									and not exists (select top 1 1
36														from #tabelle# te
37														where te.#spalte# = ''#neu#''
38														 and te.#otherPK# = t.#otherPK#
39													)'
40) , ('UE1', 'update #tabelle# set #spalte# = ''#neu#'' #xwerte# 
41									from #tabelle# t
42									where t.#spalte# = ''#alt#''
43									and not exists (select top 1 1
44														from #tabelle# te
45														where te.#spalte# = ''#neu#''
46													)'
47) , ('R', 'if exists (select top 1 1
48								from #tabelle# t
49								where t.#spalte# like  ''%#alt#%'' 
50							)
51							 begin
52								update #tabelle# set #spalte# = replace(#spalte#, ''#alt#'', ''#neu#'') #xwerte# where #spalte# like  ''%#alt#%'' 
53							 end
54							'
55) , ('RE', 'update #tabelle# 
56								set #spalte# = replace(#spalte#, ''#alt#'', ''#neu#'') #xwerte#
57							from #tabelle# t
58									where t.#spalte# like  ''%#alt#%'' 
59									and not exists (select top 1 1
60														from #tabelle# e
61														where e.#spalte# = replace(t.#spalte#, ''#alt#'', ''#neu#'') 
62													)'
63) ) as v (StatementType, StamentPattern) on s.StatementType = v.StatementType where u.Parameter1 <> u.Parameter2 ) as x select @ElementCount = @@rowcount
64 select @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top 1 @TableName = bu.Ident1 , @ColumnName = bu.Ident2 , @sqlcmd = bu.ContentFull
65 , @UID_StatementCode = bu.UID1 from @Statements bu where bu.ElementIndex = @ElementIndex if @Debugswitch > 0 begin print @SQLCmd  end exec sp_executesql
66 @SQLCmd select @ElementIndex += 1 end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow
67() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH ende: return end 
68