Back to OIM Explorer

dbo.QBM_PCheckSameCase_138

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

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

References

Referenced By

Complete Source

SQL140 lines
1CREATE PROCEDURE QBM_PCheckSameCase_1382AS3BEGIN4  DECLARE @SQLcmd nvarchar(max)5  DECLARE @cmdwork nvarchar(max)6  DECLARE @ElementBuffer QBM_YCursorBuffer7  DECLARE @ElementCount int8  DECLARE @ElementIndex int9  SET XACT_ABORT OFF10  BEGIN TRY11    INSERT INTO @ElementBuffer(Ident1,12    Ident2,13    Ident3,14    ObjectKey1)15    SELECT16      rtrim(tr.parenttable),17      rtrim(tr.childtable),18      rtrim(tr.ParentColumn) AS ParentColumn,19      rtrim(tr.ChildColumn) AS ChildColumn20    FROM QBM_VQBMRelationALL tr21    JOIN DialogColumn ic22      WITH(readpast)23      ON ic.UID_DialogTable = tr.UID_DialogTableParent AND ic.UID_DialogColumn = tr.UID_ParentColumn AND ic.SchemaDataType LIKE '%char%'24    JOIN dialogtable t125      WITH(readpast)26      ON tr.UID_DialogTableParent = t1.UID_DialogTable AND t1.tabletype IN('T',27    'B')28    JOIN QBM_VSchemaColumns isp29      ON isp.TABLE_NAME = tr.parenttable AND isp.COLUMN_NAME = tr.ParentColumn30    JOIN dialogtable t231      WITH(readpast)32      ON tr.UID_DialogTableChild = t2.UID_DialogTable AND t2.tabletype IN('T',33    'B')34    JOIN QBM_VSchemaColumns isc35      ON isc.TABLE_NAME = tr.ChildTable AND isc.COLUMN_NAME = tr.ChildColumn36    WHERE37      tr.ParentExecuteBy IN('T',38    'D') AND tr.IsDynamicFK = 139    SELECT40      @cmdwork = string_agg(CONCAT(convert(nvarchar(max), N ''), '41select distinct tx.TableName as ParentTable,  ''',42      t.TableName, ''' as ChildTable , ''XObjectKey'' as ParentColumn, ''', c.ColumnName, ''' as ChildColumn43	from ',44      t.TableName, ' x with (readpast) join DialogTable tx with (readpast) on x.', c.ColumnName, ' like concat(''<Key><T>'' , tx.TableName , ''</T>%'' )45	where dbo.QBM_FGIColumnExists(tx.tablename, ''XObjectKey'') = 146'47      ),48      '49 union all50 ')51    FROM DialogColumn c52      WITH(readpast)53    JOIN DialogTable t54      WITH(readpast)55      ON c.UID_DialogTable = t.UID_DialogTable56    WHERE57      c.IsDynamicFK = 1 AND t.TableType IN('B',58    'T') AND NOT EXISTS(59    SELECT TOP 1 160    FROM QBM_VQBMRelationALL r61    WHERE62      r.IsDynamicFK = 1 AND r.UID_DialogTableChild = c.UID_DialogTable AND r.UID_ChildColumn = c.UID_DialogColumn)63    INSERT INTO @ElementBuffer(Ident1,64    Ident2,65    Ident3,66    ObjectKey1)67    EXEC sp_executesql @cmdwork68    DECLARE @StatementBuffer QBM_YCursorbuffer69    INSERT INTO @StatementBuffer(ContentFull)70    SELECT71      CONCAT('7273			if exists (select top 1 174						 from  ',75      e.Ident2,76      ' join ',77      e.Ident1,78      ' p on p.XObjectKey = ',79      e.Ident2,80      '.',81      e.ObjectKey1,82      '83						   where ',84      e.Ident2,85      '.',86      e.ObjectKey1,87      ' <> p.XobjectKey  collate SQL_Latin1_General_CP1_CS_AS 88						)89			 begin90				update ',91      e.Ident2,92      ' set  ',93      e.ObjectKey1,94      ' = p.XObjectKey ',95    CASE96      WHEN dbo.QBM_FGIColumnExistsInSchema(e.Ident2, 'XDateUpdated') = 1 THEN97    ', ' + e.Ident2 + '.XDateUpdated = getutcdate(), ' + e.Ident2 + '.XUserUpdated =  ''' + object_name(@@procid) + ''''98    ELSE ''99    END,100    '101				  from  ',102    e.Ident2,103    ' join ',104    e.Ident1,105    ' p on p.XObjectKey = ',106    e.Ident2,107    '.',108    e.ObjectKey1,109    '110					   where ',111    e.Ident2,112    '.',113    e.ObjectKey1,114    ' <> p.XObjectKey  collate SQL_Latin1_General_CP1_CS_AS 115			 end116				')117    FROM @ElementBuffer e118    ORDER BY e.Ident2,119    e.Ident1120    SELECT @ElementCount = @@ROWCOUNT121    SELECT @ElementIndex = 1122    WHILE @ElementIndex <= @ElementCount123    BEGIN124      SELECT TOP 1 @SQLcmd = bu.ContentFull125      FROM @StatementBuffer bu126      WHERE127        bu.ElementIndex = @ElementIndex128      EXEC sp_executesql @SQLCmd129      SELECT @ElementIndex += 1130    END131  END TRY132  BEGIN CATCH133    EXEC QBM_PSessionErrorAdd DEFAULT134    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()135    RAISERROR(@Rethrow,136    18,137    1)138      WITH NOWAIT139  END CATCH140END
Open raw exported source
SQL ยท Raw40 lines
1create   procedure QBM_PCheckSameCase_138 as begin declare @SQLcmd nvarchar(max) declare @cmdwork nvarchar(max) declare @ElementBuffer QBM_YCursorBuffer2 declare @ElementCount int declare @ElementIndex int SET XACT_ABORT OFF BEGIN TRY    insert into @ElementBuffer (Ident1, Ident2, Ident3, ObjectKey1) select3 rtrim(tr.parenttable), rtrim(tr.childtable), rtrim(tr.ParentColumn) as ParentColumn, rtrim(tr.ChildColumn) as ChildColumn  from QBM_VQBMRelationALL tr4    join DialogColumn ic with (readpast) on ic.UID_DialogTable = tr.UID_DialogTableParent and ic.UID_DialogColumn = tr.UID_ParentColumn and ic.SchemaDataType5 like '%char%'  join dialogtable t1 with (readpast) on tr.UID_DialogTableParent = t1.UID_DialogTable and t1.tabletype in ('T', 'B') join QBM_VSchemaColumns6 isp on isp.TABLE_NAME = tr.parenttable and isp.COLUMN_NAME = tr.ParentColumn join dialogtable t2 with (readpast) on tr.UID_DialogTableChild = t2.UID_DialogTable7 and t2.tabletype in ('T', 'B') join QBM_VSchemaColumns isc on isc.TABLE_NAME = tr.ChildTable and isc.COLUMN_NAME = tr.ChildColumn where tr.ParentExecuteBy8 in ('T', 'D') and tr.IsDynamicFK = 1  select @cmdwork = string_agg( concat(convert(nvarchar(max), N'') , '9select distinct tx.TableName as ParentTable,  '''10, t.TableName, ''' as ChildTable , ''XObjectKey'' as ParentColumn, ''', c.ColumnName, ''' as ChildColumn11	from ', t.TableName, ' x with (readpast) join DialogTable tx with (readpast) on x.'12, c.ColumnName, ' like concat(''<Key><T>'' , tx.TableName , ''</T>%'' )13	where dbo.QBM_FGIColumnExists(tx.tablename, ''XObjectKey'') = 114') , '15 union all16 '17 )  from DialogColumn c with (readpast) join DialogTable t with (readpast) on c.UID_DialogTable = t.UID_DialogTable where c.IsDynamicFK = 1 and t.TableType18 in ('B', 'T') and not exists (select top 1 1 from QBM_VQBMRelationALL r where r.IsDynamicFK = 1 and r.UID_DialogTableChild = c.UID_DialogTable and r.UID_ChildColumn19 = c.UID_DialogColumn )  insert into @ElementBuffer (Ident1, Ident2, Ident3, ObjectKey1) exec sp_executesql @cmdwork  declare @StatementBuffer QBM_YCursorbuffer20 insert into @StatementBuffer(ContentFull) select CONCAT('2122			if exists (select top 1 123						 from  ', e.Ident2, ' join ', e.Ident1 , ' p on p.XObjectKey = '24, e.Ident2, '.', e.ObjectKey1 , '25						   where ', e.Ident2, '.', e.ObjectKey1 , ' <> p.XobjectKey  collate SQL_Latin1_General_CP1_CS_AS 26						)27			 begin28				update '29, e.Ident2, ' set  ', e.ObjectKey1 , ' = p.XObjectKey ' , case when dbo.QBM_FGIColumnExistsInSchema(e.Ident2, 'XDateUpdated') = 1 then ', ' + e.Ident2 30+'.XDateUpdated = getutcdate(), '+ e.Ident2 +'.XUserUpdated =  ''' + object_name(@@procid) + '''' else '' end ,'31				  from  ', e.Ident2, ' join ', e.Ident132 , ' p on p.XObjectKey = ', e.Ident2, '.', e.ObjectKey1 , '33					   where ', e.Ident2, '.', e.ObjectKey1 , ' <> p.XObjectKey  collate SQL_Latin1_General_CP1_CS_AS 34			 end35				'36 ) from @ElementBuffer e order by e.Ident2, e.Ident1   select @ElementCount = @@ROWCOUNT select @ElementIndex = 1 while @ElementIndex <= @ElementCount 37begin select top 1 @SQLcmd = bu.ContentFull from @StatementBuffer bu where bu.ElementIndex = @ElementIndex exec sp_executesql @SQLCmd select @ElementIndex38 += 1 end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow,39 18, 1) WITH NOWAIT END CATCH end 40