Back to OIM Explorer

dbo.QBM_PGUIDReplaceLight_Multi

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 6.857 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 source dbo.QBM_FGIColumnExists source text reference
  • references source dbo.QBM_FGIColumnExistsInSchema source text reference
  • references source dbo.QBM_FGISessionErrorRethrow 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

SQL240 lines
1CREATE PROCEDURE QBM_PGUIDReplaceLight_Multi(2  @StartElements QBM_YParameterlist READONLY,3  @ReplaceValues QBM_YParameterlist READONLY,4  @IncludeLob BIT = 15)6AS7BEGIN8  DECLARE @TableName varchar(30)9  DECLARE @ColumnName varchar(30)10  DECLARE @sqlcmd nvarchar(max)11  DECLARE @Debugswitch int = 012  DECLARE @Statements QBM_YCursorBuffer13  DECLARE @ElementCount int14  DECLARE @ElementIndex int15  DECLARE @UID_StatementCode varchar(38)16  SET XACT_ABORT OFF17  BEGIN TRY18    IF @Debugswitch = 019    BEGIN20      IF EXISTS(21        SELECT TOP 1 122        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      SELECT TOP 1 130      FROM sys.foreign_keys fk31        WITH(readpast)32      JOIN sys.objects c33        WITH(readpast)34        ON c.object_id = fk.parent_object_id35      JOIN sys.objects p36        WITH(readpast)37        ON p.object_id = fk.referenced_object_id38      WHERE39        fk.type = 'F' AND c.type IN('U') AND p.type IN('U') AND fk.is_disabled = 0)40      BEGIN41        RAISERROR('#LDS#Usage of the procedure is only allowed when ALL triggers and FK-constraints are switched off.|',42        18,43        1)44          WITH nowait45      END46    END47    INSERT INTO @Statements(ContentFull,48    Ident1,49    Ident2,50    UID1)51    SELECT52      x.StatementCode,53      x.TableName,54      x.ColumnName,55      dbo.QBM_FCVStringToGUID('',56      x.StatementCode)57    FROM(58    SELECT59      replace(replace(replace(replace(replace(replace(v.StamentPattern, '#tabelle#', s.TableName), '#spalte#',60      s.ColumnName), '#alt#', u.Parameter1), '#neu#', u.Parameter2), '#otherPK#', s.OtherPK), '#xwerte#', CASE61      WHEN dbo.QBM_FGIColumnExistsInSchema(s.TableName, 'XDateUpdated') = 1 AND s.ColumnName NOT LIKE 'X[d,u][a,s][t,e][e,r]updated' THEN62      ', XDateUpdated = getutcdate(), XUserUpdated =  ''' + object_name(@@procid) + ''''63    ELSE ''64    END) AS StatementCode,65    s.TableName,66    s.ColumnName67    FROM @ReplaceValues u CROSS68    JOIN(69    SELECT70      r.ChildTable AS TableName, r.ChildColumn AS ColumnName, 'U' AS StatementType, '' AS OtherPK71    FROM QBM_VQBMRelation r72    JOIN DialogColumn c73      WITH(readpast)74      ON r.UID_ChildColumn = c.UID_DialogColumn75    JOIN @StartElements st76      ON st.Parameter1 = r.ParentTable77    WHERE78      c.IsPKMember = 079    UNION80    SELECT81      r.ChildTable AS TableName, r.ChildColumn AS ColumnName, 'UE' AS StatementType, ra.ChildColumn AS OtherPK82    FROM QBM_VQBMRelation r83    JOIN DialogColumn c84      WITH(readpast)85      ON r.UID_ChildColumn = c.UID_DialogColumn86    JOIN QBM_VQBMRelation ra87      ON r.UID_DialogTableChild = ra.UID_DialogTableChild AND ra.UID_QBMRelation <> r.UID_QBMRelation88    JOIN @StartElements st89      ON st.Parameter1 = r.ParentTable90    WHERE91      c.IsPKMember = 192    UNION93    SELECT94      r.ChildTable, c.ColumnName, 'RE', '' AS OtherPK95    FROM QBM_VQBMRelation r96    JOIN Dialogcolumn c97      WITH(readpast)98      ON r.UID_DialogTableChild = c.UID_DialogTable AND c.ColumnName = 'XObjectKey'99    JOIN DialogTable t100      WITH(readpast)101      ON r.UID_DialogTableChild = t.UID_DialogTable AND t.isMNTable = 1102    JOIN @StartElements st103      ON st.Parameter1 = r.ParentTable104    UNION105    SELECT106      st.Parameter1 AS StartTable, st.Parameter2 AS StartColumn, 'UE1', '' AS OtherPK107    FROM @StartElements st108    UNION109    SELECT110      st.Parameter1, 'XObjectKey', 'RE', '' AS OtherPK111    FROM @StartElements st112    UNION113    SELECT114      'QBMTaggedChange', 'ObjectKey', 'R', '' AS OtherPK115    UNION116    SELECT117      'QBMBufferConfig', 'ObjectKeyOfRow', 'R', '' AS OtherPK118    UNION119    SELECT120      'DialogWatchOperation', 'ObjectKeyOfRow', 'R', '' AS OtherPK121    UNION122    SELECT123      'DialogTaggedItem', 'ObjectKey', 'R', '' AS OtherPK124    UNION125    SELECT126      tr.TableName, c.ColumnName, 'R', '' AS OtherPK127    FROM DialogValidDynamicRef r128      WITH(readpast)129    JOIN DialogTable t130      WITH(readpast)131      ON r.UID_DialogTableReference = t.UID_DialogTable132    JOIN DialogColumn c133      WITH(readpast)134      ON r.UID_DialogColumn = c.UID_DialogColumn135    JOIN DialogTable tr136      WITH(readpast)137      ON c.UID_DialogTable = tr.UID_DialogTable138    JOIN @StartElements st139      ON st.Parameter1 = t.TableName140    UNION141    SELECT142      t.TableName, c.ColumnName, 'R', '' AS OtherPK143    FROM DialogColumn c144      WITH(readpast)145    JOIN DialogTable t146      WITH(readpast)147      ON c.UID_DialogTable = t.UID_DialogTable148    LEFT149    OUTER150    JOIN DialogValidDynamicRef r151      WITH(readpast)152      ON r.UID_DialogColumn = c.UID_DialogColumn153    WHERE154      c.IsDynamicFK = 1 AND r.UID_DialogColumn IS NULL155    UNION156    SELECT157      t.TableName, c.ColumnName, 'R', '' AS OtherPK158    FROM DialogColumn c159      WITH(readpast)160    JOIN DialogTable t161      WITH(readpast)162      ON c.UID_DialogTable = t.UID_DialogTable163    WHERE164      (c.SyntaxType = 'XML' OR c.SyntaxType LIKE 'SQL%' OR c.SyntaxType LIKE 'VB%') AND c.SchemaDataLen <> 138 AND @IncludeLob = 1) s165    JOIN DialogTable te166      WITH(readpast)167      ON te.TableName = s.TableName AND te.TableType IN('B', 'T')168    JOIN INFORMATION_SCHEMA.COLUMNS ic169      WITH(readpast)170      ON ic.TABLE_NAME = te.TableName AND s.ColumnName = ic.COLUMN_NAME171    JOIN sys.tables t172      ON te.TableName = t.name AND t.type = 'U' AND t.is_memory_optimized = 0 AND ic.DATA_TYPE LIKE '%char%'173    JOIN(174    VALUES('U', 'update #tabelle# set #spalte# = ''#neu#'' #xwerte# where #spalte# = ''#alt#'''),('UE', 'update #tabelle# 175										set #spalte# = ''#neu#'' #xwerte#176									from #tabelle# t177									where t.#spalte# = ''#alt#''178									and not exists (select top 1 1179														from #tabelle# te180														where te.#spalte# = ''#neu#''181														 and te.#otherPK# = t.#otherPK#182													)'183    ),('UE1', 'update #tabelle# set #spalte# = ''#neu#'' #xwerte# 184									from #tabelle# t185									where t.#spalte# = ''#alt#''186									and not exists (select top 1 1187														from #tabelle# te188														where te.#spalte# = ''#neu#''189													)'190    ),('R', 'if exists (select top 1 1191								from #tabelle# t192								where t.#spalte# like  ''%#alt#%'' 193							)194							 begin195								update #tabelle# set #spalte# = replace(#spalte#, ''#alt#'', ''#neu#'') #xwerte# where #spalte# like  ''%#alt#%'' 196							 end197							'198    ),('RE', 'update #tabelle# 199								set #spalte# = replace(#spalte#, ''#alt#'', ''#neu#'') #xwerte#200							from #tabelle# t201									where t.#spalte# like  ''%#alt#%'' 202									and not exists (select top 1 1203														from #tabelle# e204														where e.#spalte# = replace(t.#spalte#, ''#alt#'', ''#neu#'') 205													)'206    )) AS v(StatementType, StamentPattern)207      ON s.StatementType = v.StatementType208    WHERE209      u.Parameter1 <> u.Parameter2) AS x210    SELECT @ElementCount = @@rowcount211    SELECT @ElementIndex = 1212    WHILE @ElementIndex <= @ElementCount213    BEGIN214      SELECT215        TOP 1 @TableName = bu.Ident1,216        @ColumnName = bu.Ident2,217        @sqlcmd = bu.ContentFull,218        @UID_StatementCode = bu.UID1219      FROM @Statements bu220      WHERE221        bu.ElementIndex = @ElementIndex222      IF @Debugswitch > 0223      BEGIN224        print @SQLCmd225      END226      EXEC sp_executesql @SQLCmd227      SELECT @ElementIndex += 1228    END229  END TRY230  BEGIN CATCH231    EXEC QBM_PSessionErrorAdd DEFAULT232    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()233    RAISERROR(@Rethrow,234    18,235    1)236      WITH NOWAIT237  END CATCH238  ende:239  RETURN240END
Open raw exported source
SQL ยท Raw68 lines
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 (select6 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 (readpast7) 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_FCVStringToGUID9('', x.StatementCode) from ( select replace(replace(replace(replace(replace(replace(v.StamentPattern , '#tabelle#', s.TableName) , '#spalte#', s.ColumnName10) , '#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 ColumnName13, 'U' as StatementType, '' as OtherPK from QBM_VQBMRelation r join DialogColumn c with (readpast) on r.UID_ChildColumn = c.UID_DialogColumn join @StartElements14 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.ParentTable17 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_DialogTableChild18 = 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 OtherPK20  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 DialogValidDynamicRef23 r with (readpast) join DialogTable t with (readpast) on r.UID_DialogTableReference = t.UID_DialogTable join DialogColumn c with (readpast) on r.UID_DialogColumn24 = c.UID_DialogColumn join DialogTable tr with (readpast) on c.UID_DialogTable = tr.UID_DialogTable join @StartElements st on st.Parameter1 = t.TableName25  union select t.TableName, c.ColumnName, 'R', '' as OtherPK from DialogColumn c with (readpast) join DialogTable t with (readpast) on c.UID_DialogTable26 = t.UID_DialogTable left outer join DialogValidDynamicRef r with (readpast) on r.UID_DialogColumn = c.UID_DialogColumn where c.IsDynamicFK = 1 and r.UID_DialogColumn27 is null  union select t.TableName, c.ColumnName, 'R', '' as OtherPK from DialogColumn c with (readpast) join DialogTable t with (readpast) on c.UID_DialogTable28 = 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 (readpast30) 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_optimized31 = 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# t34									where t.#spalte# = ''#alt#''35									and not exists (select top 1 136														from #tabelle# te37														where te.#spalte# = ''#neu#''38														 and te.#otherPK# = t.#otherPK#39													)'40) , ('UE1', 'update #tabelle# set #spalte# = ''#neu#'' #xwerte# 41									from #tabelle# t42									where t.#spalte# = ''#alt#''43									and not exists (select top 1 144														from #tabelle# te45														where te.#spalte# = ''#neu#''46													)'47) , ('R', 'if exists (select top 1 148								from #tabelle# t49								where t.#spalte# like  ''%#alt#%'' 50							)51							 begin52								update #tabelle# set #spalte# = replace(#spalte#, ''#alt#'', ''#neu#'') #xwerte# where #spalte# like  ''%#alt#%'' 53							 end54							'55) , ('RE', 'update #tabelle# 56								set #spalte# = replace(#spalte#, ''#alt#'', ''#neu#'') #xwerte#57							from #tabelle# t58									where t.#spalte# like  ''%#alt#%'' 59									and not exists (select top 1 160														from #tabelle# e61														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 = @@rowcount64 select @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top 1 @TableName = bu.Ident1 , @ColumnName = bu.Ident2 , @sqlcmd = bu.ContentFull65 , @UID_StatementCode = bu.UID1 from @Statements bu where bu.ElementIndex = @ElementIndex if @Debugswitch > 0 begin print @SQLCmd  end exec sp_executesql66 @SQLCmd select @ElementIndex += 1 end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow67() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH ende: return end 68