Back to OIM Explorer

dbo.QBM_PForeignKeyEmptySetNull

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 3.013 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_PExecuteSQLMulti source text reference
references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

Complete Source

1CREATE PROCEDURE QBM_PForeignKeyEmptySetNull(2  @ChildTableName varchar(64) = '%'3)4AS5BEGIN6  DECLARE @ElementBuffer QBM_YCursorBuffer7  BEGIN TRY8    INSERT INTO @ElementBuffer(ContentFull)9    SELECT10      CONCAT('11 if exists (select top 1 1 12			from ',13      tc.TableName,14      ' with (nolock)15			where ',16      c.columnname,17      ' = ''''18				or (',19      c.columnname,20      ' not like ''%[0-9,a-z]%'' and ',21      c.columnname,22      ' > '' '' )23			)24  begin25	update ',26      tc.TableName,27      ' set ',28      c.columnname,29      ' = null ',30    CASE31      WHEN dbo.QBM_FGIColumnExistsInSchema(tc.TableName, 'XDateUpdated') = 1 AND c.ColumnName NOT LIKE 'X[d,u][a,s][t,e][e,r]updated' THEN32    ', XDateUpdated = getutcdate(), XUserUpdated =  ''' + object_name(@@procid) + ''''33    ELSE ''34    END,35    ' where rtrim(',36    c.columnname,37    ') = '''' or (',38    c.columnname,39    ' not like ''%[0-9,a-z]%'' and ',40    c.columnname,41    ' is not null)42  end43	')44    FROM QBM_VQBMRelation tr45    JOIN dialogcolumn c46      WITH(readpast)47      ON tr.uid_childcolumn = c.uid_dialogColumn48    JOIN dialogtable tc49      WITH(readpast)50      ON c.uid_dialogtable = tc.uid_dialogtable51    JOIN information_schema.columns ic52      WITH(readpast)53      ON ic.table_name = tc.TableName AND ic.column_name = c.columnname AND ic.DATA_TYPE = 'varchar' AND ic.IS_NULLABLE = 'YES' AND ic.CHARACTER_MAXIMUM_LENGTH54    IN(38)55    JOIN information_schema.tables it56      WITH(readpast)57      ON ic.table_name = it.table_name AND it.table_type = 'base table'58    WHERE59      isnull(tr.BaseRelationID,60    '') = '' AND tr.ParentExecuteBy IN('T',61    'D') AND tr.childtable LIKE @ChildTableName62    UNION63    SELECT64      CONCAT('65 if exists (select top 1 1 66			from ',67      t.TableName,68      ' with (nolock)69			where ',70      c.columnname,71      ' = ''''72				or (',73      c.columnname,74      ' not like ''%[0-9,a-z]%'' and ',75      c.columnname,76      ' > '' '' )77			)78  begin79	update ',80      t.TableName,81      ' set ',82      c.columnname,83      ' = null ',84    CASE85      WHEN dbo.QBM_FGIColumnExistsInSchema(t.TableName, 'XDateUpdated') = 1 AND c.ColumnName NOT LIKE 'X[d,u][a,s][t,e][e,r]updated' THEN86    ', XDateUpdated = getutcdate(), XUserUpdated =  ''' + object_name(@@procid) + ''''87    ELSE ''88    END,89    'where rtrim(',90    c.columnname,91    ') = '''' or (',92    c.columnname,93    ' not like ''%[0-9,a-z]%'' and ',94    c.columnname,95    ' is not null)96  end97	')98    FROM dialogcolumn c99      WITH(readpast)100    JOIN DialogTable t101      WITH(readpast)102      ON c.UID_DialogTable = t.UID_DialogTable103    JOIN information_schema.columns ic104      WITH(readpast)105      ON ic.table_name = t.TableName AND ic.column_name = c.columnname AND ic.DATA_TYPE = 'varchar' AND ic.IS_NULLABLE = 'YES' AND ic.CHARACTER_MAXIMUM_LENGTH106    IN(138)107    JOIN information_schema.tables it108      WITH(readpast)109      ON ic.table_name = it.table_name AND it.table_type = 'base table'110    WHERE111      c.ColumnName <> 'XObjectKey' AND t.TableName LIKE @ChildTableName112    EXEC QBM_PExecuteSQLMulti @ElementBuffer,113    0,114      @@PROCID,115      @LockTimeout_ms = 2500,116      @MaxWaitTimeForLock_s = 7117  END TRY118  BEGIN CATCH119    EXEC QBM_PSessionErrorAdd DEFAULT120    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()121    RAISERROR(@Rethrow,122    18,123    1)124      WITH NOWAIT125  END CATCH126  ende:127END

Open raw exported source

1    create   procedure QBM_PForeignKeyEmptySetNull (@ChildTableName varchar(64) = '%' ) as begin  declare @ElementBuffer QBM_YCursorBuffer BEGIN2 TRY insert into @ElementBuffer (ContentFull) select concat('3 if exists (select top 1 1 4			from ' , tc.TableName , ' with (nolock)5			where ' , c.columnname6 , ' = ''''7				or (' , c.columnname , ' not like ''%[0-9,a-z]%'' and ' , c.columnname , ' > '' '' )8			)9  begin10	update ' , tc.TableName , ' set '11 , c.columnname , ' = null ' , case when dbo.QBM_FGIColumnExistsInSchema(tc.TableName, 'XDateUpdated') = 1 and c.ColumnName not like 'X[d,u][a,s][t,e][e,r]updated'12 then ', XDateUpdated = getutcdate(), XUserUpdated =  ''' + object_name(@@procid) + '''' else '' end ,' where rtrim(' , c.columnname , ') = '''' or (' 13, c.columnname , ' not like ''%[0-9,a-z]%'' and ' , c.columnname , ' is not null)14  end15	') from QBM_VQBMRelation tr  join dialogcolumn c with (readpast16) on tr.uid_childcolumn = c.uid_dialogColumn join dialogtable tc with (readpast) on c.uid_dialogtable = tc.uid_dialogtable join information_schema.columns17 ic with (readpast) on ic.table_name = tc.TableName and ic.column_name = c.columnname and ic.DATA_TYPE = 'varchar' and ic.IS_NULLABLE = 'YES' and ic.CHARACTER_MAXIMUM_LENGTH18 in( 38) join information_schema.tables it with (readpast) on ic.table_name = it.table_name and it.table_type = 'base table' where isnull(tr.BaseRelationID19, '') = '' and tr.ParentExecuteBy in ('T', 'D') and tr.childtable like @ChildTableName union select concat('20 if exists (select top 1 1 21			from ' , 22t.TableName , ' with (nolock)23			where ' , c.columnname , ' = ''''24				or (' , c.columnname , ' not like ''%[0-9,a-z]%'' and ' , c.columnname , ' > '' '' )25			)26  begin27	update '28 , t.TableName , ' set ' , c.columnname , ' = null ' , case when dbo.QBM_FGIColumnExistsInSchema(t.TableName, 'XDateUpdated') = 1 and c.ColumnName not 29like 'X[d,u][a,s][t,e][e,r]updated' then ', XDateUpdated = getutcdate(), XUserUpdated =  ''' + object_name(@@procid) + '''' else '' end ,'where rtrim('30 , c.columnname , ') = '''' or (' , c.columnname , ' not like ''%[0-9,a-z]%'' and ' , c.columnname , ' is not null)31  end32	') from dialogcolumn c with33 (readpast) join DialogTable t with (readpast) on c.UID_DialogTable = t.UID_DialogTable join information_schema.columns ic with (readpast) on ic.table_name34 = t.TableName and ic.column_name = c.columnname and ic.DATA_TYPE = 'varchar' and ic.IS_NULLABLE = 'YES' and ic.CHARACTER_MAXIMUM_LENGTH in( 138) join 35information_schema.tables it with (readpast) on ic.table_name = it.table_name and it.table_type = 'base table' where c.ColumnName <> 'XObjectKey' and t.TableName36 like @ChildTableName exec QBM_PExecuteSQLMulti @ElementBuffer, 0, @@PROCID  , @LockTimeout_ms = 2500 , @MaxWaitTimeForLock_s = 7 END TRY BEGIN CATCH exec37 QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH ende:38  end 39