dbo.QBM_PForeignKeyEmptySetNull

1    create   procedure QBM_PForeignKeyEmptySetNull (@ChildTableName varchar(64) = '%' ) as begin  declare @ElementBuffer QBM_YCursorBuffer BEGIN
2 TRY insert into @ElementBuffer (ContentFull) select concat('
3 if exists (select top 1 1 
4			from ' , tc.TableName , ' with (nolock)
5			where ' , c.columnname
6 , ' = ''''
7				or (' , c.columnname , ' not like ''%[0-9,a-z]%'' and ' , c.columnname , ' > '' '' )
8			)
9  begin
10	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  end
15	') from QBM_VQBMRelation tr  join dialogcolumn c with (readpast
16) on tr.uid_childcolumn = c.uid_dialogColumn join dialogtable tc with (readpast) on c.uid_dialogtable = tc.uid_dialogtable join information_schema.columns
17 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_LENGTH
18 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.BaseRelationID
19, '') = '' 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  begin
27	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  end
32	') from dialogcolumn c with
33 (readpast) join DialogTable t with (readpast) on c.UID_DialogTable = t.UID_DialogTable join information_schema.columns ic with (readpast) on ic.table_name
34 = 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.TableName
36 like @ChildTableName exec QBM_PExecuteSQLMulti @ElementBuffer, 0, @@PROCID  , @LockTimeout_ms = 2500 , @MaxWaitTimeForLock_s = 7 END TRY BEGIN CATCH exec
37 QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH ende:
38  end 
39