Back to OIM Explorer

dbo.QBM_PColumnSQLReplace

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 1.631 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

  • No direct source references extracted.

Complete Source

SQL88 lines
1CREATE PROCEDURE QBM_PColumnSQLReplace(2  @search nvarchar(400),3  @replace nvarchar(400)4)5AS6BEGIN7  DECLARE @cmd nvarchar(max)8  DECLARE @DebugSwitch int = 09  DECLARE @CountItems int10  DECLARE @ElementBuffer QBM_YCursorBuffer11  DECLARE @ElementCount int12  DECLARE @ElementIndex int13  SET XACT_ABORT OFF14  BEGIN TRY15    SET nocount16      ON17    INSERT INTO @ElementBuffer(ContentFull)18    SELECT19      CONCAT('20			update ',21      x.TableName,22      ' set ',23      x.ColumnName,24      ' = REPLACE(',25      x.ColumnName,26      ', ''',27      @search,28      ''', ''',29      @replace,30      ''')31				',32    CASE dbo.QBM_FGIColumnExistsInSchema(x.TableName, 'XDateUpdated')33      WHEN 1 THEN34    ', XDateUpdated = getutcdate(), XUserUpdated =  ''' + object_name(@@procid) + ''''35    ELSE ''36    END,37    '38				where ',39    x.ColumnName,40    ' like ''%',41    @search,42    '%'' collate SQL_Latin1_General_CP1_CS_AS /* 35127 CS*/43				')44    FROM(45    SELECT46      c.columnname,47      t.TableName48    FROM dialogcolumn c49    JOIN DialogTable t50      ON c.UID_DialogTable = t.UID_DialogTable51    WHERE52      SyntaxType LIKE '%sql%'53    UNION54    SELECT55      'Columnname',56      'dialogColumn') AS x57    SELECT @ElementCount = @@ROWCOUNT58    SELECT @ElementIndex = 159    WHILE @ElementIndex <= @ElementCount60    BEGIN61      SELECT TOP 1 @cmd = bu.ContentFull62      FROM @ElementBuffer bu63      WHERE64        bu.ElementIndex = @ElementIndex65      IF @DebugSwitch > 066      BEGIN67        print @cmd68      END69      EXEC sp_executesql @cmd70      SELECT @CountItems = @@ROWCOUNT71      IF @CountItems > 072      BEGIN73        print str(@CountItems) + ' rows found' print @cmd74      END75      SELECT @ElementIndex += 176    END77  END TRY78  BEGIN CATCH79    EXEC QBM_PSessionErrorAdd DEFAULT80    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()81    RAISERROR(@Rethrow,82    18,83    1)84      WITH NOWAIT85  END CATCH86  endLabel:87  RETURN88END
Open raw exported source
SQL ยท Raw16 lines
1   create   procedure QBM_PColumnSQLReplace (@search nvarchar(400) , @replace nvarchar(400) ) as begin declare @cmd nvarchar(max) declare @DebugSwitch2 int = 0 declare @CountItems int declare @ElementBuffer QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int SET XACT_ABORT OFF BEGIN 3TRY set nocount on insert into @ElementBuffer (ContentFull) select  concat('4			update ' , x.TableName , ' set ' , x.ColumnName , ' = REPLACE(' , x.ColumnName5 , ', ''' , @search , ''', ''' , @replace , ''')6				' , case dbo.QBM_FGIColumnExistsInSchema(x.TableName, 'XDateUpdated') when 1 then ', XDateUpdated = getutcdate(), XUserUpdated =  '''7 + object_name(@@procid) + '''' else '' end ,'8				where ' , x.ColumnName , ' like ''%' , @search , '%'' collate SQL_Latin1_General_CP1_CS_AS /* 35127 CS*/9				'10 ) from ( select c.columnname, t.TableName from dialogcolumn c join DialogTable t on c.UID_DialogTable = t.UID_DialogTable where SyntaxType like '%sql%'11 union select 'Columnname', 'dialogColumn' ) as x select @ElementCount = @@ROWCOUNT select @ElementIndex = 1 while @ElementIndex <= @ElementCount begin12 select top 1 @cmd = bu.ContentFull from @ElementBuffer bu where bu.ElementIndex = @ElementIndex if @DebugSwitch > 0 begin print @cmd end exec sp_executesql13 @cmd select @CountItems = @@ROWCOUNT if @CountItems > 0 begin print str(@CountItems) + ' rows found' print @cmd end select @ElementIndex += 1 end  END14 TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT15 END CATCH endLabel:  return end 16