Back to OIM Explorer

dbo.QBM_PForeignKeyObjectKeyNoRef

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 4.789 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_FCVObjectkeyToElement 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_PSessionErrorAdd source text reference

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL228 lines
1CREATE PROCEDURE QBM_PForeignKeyObjectKeyNoRef(2  @ObjectKeyDialogColumn varchar(138),3  @RepairIt BIT4)5AS6BEGIN7  DECLARE @TableName varchar(30)8  DECLARE @columnname varchar(30)9  DECLARE @MinLen int10  DECLARE @DebugSwitch int = 011  DECLARE @SQLcmd nvarchar(max)12  DECLARE @RefTables QBM_YSingleGUID13  DECLARE @RefTable varchar(30)14  DECLARE @Results QBM_YCursorBuffer15  SET XACT_ABORT OFF16  BEGIN TRY17    SET nocount18      ON19    SELECT20      TOP 1 @TableName = t.TableName,21      @columnname = c.ColumnName,22      @MinLen = CASE cc.IS_NULLABLE23      WHEN 'YES' THEN24      025    ELSE 126    END | sign(isnull(c.MinLen, 0))27    FROM DialogColumn c28      WITH(readpast)29    JOIN DialogTable t30      WITH(readpast)31      ON c.UID_DialogTable = t.UID_DialogTable32    JOIN INFORMATION_SCHEMA.COLUMNS cc33      WITH(readpast)34      ON cc.TABLE_NAME = t.TableName AND cc.COLUMN_NAME = c.ColumnName35    WHERE36      c.XObjectKey = @ObjectKeyDialogColumn37    IF @DebugSwitch > 038    BEGIN39      print 'verarbeitung ' + @TableName + '.' + @Columnname print '		minlen' + str(@minlen)40    END41    SELECT42      @SQLcmd = '43		select distinct dbo.QBM_FCVObjectkeyToElement(''TableName'', ' + @columnname + ')44			from ' + @TableName + ' with (readpast)45			where '46      + @columnname + ' > '' ''47			'48    DELETE @RefTables49    INSERT INTO @RefTables(UID_SingleGuid)50    EXEC sp_executeSql @SQLcmd51    IF @DebugSwitch > 052    BEGIN53      SELECT *54      FROM @RefTables55    END56    WHILE EXISTS(57    SELECT TOP 1 158    FROM @RefTables)59    BEGIN60      SELECT TOP 1 @RefTable = UID_SingleGuid61      FROM @RefTables62      IF @DebugSwitch > 063      BEGIN64        print '@RefTable ' + isnull(@RefTable,65        '<none>')66      END67      IF EXISTS(68        SELECT TOP 1 169        FROM DialogTable t70        WITH(readpast)71      JOIN DialogColumn c72        WITH(readpast)73        ON t.UID_DialogTable = c.UID_DialogTable74      WHERE75        t.TableName = @RefTable AND c.ColumnName = 'XObjectKey' AND t.TableType IN('T', 'V'))76      BEGIN77        IF @RepairIt = 078        BEGIN79          SELECT80            @SQLcmd = '			81				select ''' + @TableName + ''', ''' + @columnname + ''', ' + str(@MinLen) + ', x.' + @columnname + '82					from ' + @TableName83            + ' x with (readpast) left outer join ' + @RefTable + ' y with (readpast) on x.' + @columnname + ' = y.XObjectKey84					where x.' + @columnname85            + ' like ''<Key><T>'' + ''' + @RefTable + ''' + ''</T>%''86					 and y.XObjectKey is null87						'88        END89        ELSE90        BEGIN91          IF @MinLen > 092          BEGIN93            SELECT94              @SQLcmd = '			95					delete ' + @TableName + '96						from ' + @TableName + ' x left outer join ' + @RefTable + ' y  on x.' + @columnname97              + ' = y.XObjectKey98						where x.' + @columnname + ' like ''<Key><T>'' + ''' + @RefTable + ''' + ''</T>%''99						 and y.XObjectKey is null100							'101          END102          ELSE103          BEGIN104            SELECT105              @SQLcmd = CONCAT('			106					update ',107              @TableName,108              '109						set ',110              @columnname,111              ' = null ',112            CASE113              WHEN dbo.QBM_FGIColumnExistsInSchema(@TableName, 'XDateUpdated') = 1 AND @columnname NOT LIKE 'X[d,u][a,s][t,e][e,r]updated' THEN114            ', XDateUpdated = getutcdate(), XUserUpdated =  ''' + object_name(@@procid) + ''''115            ELSE ''116            END,117            ' from ',118            @TableName,119            ' x left outer join ',120            @RefTable,121            ' y  on x.',122            @columnname,123            ' = y.XObjectKey124						where x.',125            @columnname,126            ' like ''<Key><T>'' + ''',127            @RefTable,128            ''' + ''</T>%''129						 and y.XObjectKey is null130							')131          END132        END133      END134      ELSE135      BEGIN136        IF @RepairIt = 0137        BEGIN138          SELECT139            @SQLcmd = '			140				select ''' + @TableName + ''', ''' + @columnname + ''', ' + str(@MinLen) + ', x.' + @columnname + '141					from ' + @TableName142            + ' x with (readpast)143					where x.' + @columnname + ' like ''<Key><T>'' + ''' + @RefTable + ''' + ''</T>%''144						'145        END146        ELSE147        BEGIN148          IF @MinLen > 0149          BEGIN150            SELECT151              @SQLcmd = '			152					delete ' + @TableName + '153						from ' + @TableName + ' x 154						where x.' + @columnname + ' like ''<Key><T>'' + '''155              + @RefTable + ''' + ''</T>%''156							'157          END158          ELSE159          BEGIN160            SELECT161              @SQLcmd = CONCAT('			162					update ',163              @TableName + '164						set ',165              @columnname,166              ' = null ',167            CASE168              WHEN dbo.QBM_FGIColumnExistsInSchema(@TableName, 'XDateUpdated') = 1 AND @columnname NOT LIKE 'X[d,u][a,s][t,e][e,r]updated' THEN169            ', XDateUpdated = getutcdate(), XUserUpdated =  ''' + object_name(@@procid) + ''''170            ELSE ''171            END,172            ' from ',173            @TableName,174            ' x 175						where x.',176            @columnname,177            ' like ''<Key><T>'' + ''',178            @RefTable,179            ''' + ''</T>%''180							')181          END182        END183      END184      IF @RepairIt = 0185      BEGIN186        IF @DebugSwitch > 0187        BEGIN188          print @Sqlcmd189        END190        INSERT INTO @Results(Ident1,191        ident2,192        int1,193        ObjectKey1)194        EXEC sp_executeSQL @SQLcmd195      END196      ELSE197      BEGIN198        IF @DebugSwitch > 0199        BEGIN200          print @Sqlcmd201        END202        EXEC sp_executeSQL @SQLcmd203      END204      DELETE @RefTables205      WHERE206        UID_SingleGuid = @RefTable207    END208    IF @RepairIt = 0209    BEGIN210      SELECT211        Ident1 AS TableName,212        ident2 AS ColumnName,213        int1 AS MinLen,214        ObjectKey1 AS ObjectKeyInvalid215      FROM @Results216    END217  END TRY218  BEGIN CATCH219    EXEC QBM_PSessionErrorAdd DEFAULT220    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()221    RAISERROR(@Rethrow,222    18,223    1)224      WITH NOWAIT225  END CATCH226  ende:227  RETURN228END
Open raw exported source
SQL ยท Raw63 lines
1     create   procedure QBM_PForeignKeyObjectKeyNoRef (@ObjectKeyDialogColumn varchar(138) , @RepairIt bit ) as begin declare @TableName varchar2(30) declare @columnname varchar(30) declare @MinLen int declare @DebugSwitch int = 0 declare @SQLcmd nvarchar(max) declare @RefTables QBM_YSingleGUID 3declare @RefTable varchar(30) declare @Results QBM_YCursorBuffer     SET XACT_ABORT OFF BEGIN TRY set nocount on select top 1 @TableName = t.TableName 4, @columnname = c.ColumnName , @MinLen = case cc.IS_NULLABLE when 'YES' then 0 else 1 end | sign(isnull(c.MinLen, 0)) from DialogColumn c with (readpast5) join DialogTable t with (readpast) on c.UID_DialogTable = t.UID_DialogTable join INFORMATION_SCHEMA.COLUMNS cc with (readpast) on cc.TABLE_NAME = t.TableName6 and cc.COLUMN_NAME = c.ColumnName where c.XObjectKey = @ObjectKeyDialogColumn if @DebugSwitch > 0 begin print 'verarbeitung ' + @TableName + '.' + @Columnname7 print '		minlen' + str(@minlen) end select @SQLcmd = '8		select distinct dbo.QBM_FCVObjectkeyToElement(''TableName'', ' + @columnname + ')9			from '10 + @TableName + ' with (readpast)11			where ' + @columnname + ' > '' ''12			'  delete  @RefTables  insert into @RefTables(UID_SingleGuid ) exec sp_executeSql13 @SQLcmd if @DebugSwitch > 0 begin select * from @RefTables end while exists (select top 1 1 from @RefTables ) begin select top 1 @RefTable = UID_SingleGuid14  from @RefTables if @DebugSwitch > 0 begin print '@RefTable ' + isnull(@RefTable, '<none>') end if exists (select top 1 1 from DialogTable t with (readpast15) join DialogColumn c with (readpast) on t.UID_DialogTable = c.UID_DialogTable where t.TableName = @RefTable and c.ColumnName = 'XObjectKey'  and t.TableType16 in ('T', 'V')  ) begin if @RepairIt = 0 begin  select @SQLcmd = '			17				select ''' + @TableName + ''', ''' + @columnname + ''', ' + str(@MinLen) + ', x.'18 + @columnname + '19					from ' + @TableName + ' x with (readpast) left outer join ' + @RefTable + ' y with (readpast) on x.' + @columnname + ' = y.XObjectKey20					where x.'21 + @columnname + ' like ''<Key><T>'' + ''' + @RefTable + ''' + ''</T>%''22					 and y.XObjectKey is null23						' end else begin if @MinLen > 0 begin  24select @SQLcmd = '			25					delete ' + @TableName + '26						from ' + @TableName + ' x left outer join ' + @RefTable + ' y  on x.' + @columnname + ' = y.XObjectKey27						where x.'28 + @columnname + ' like ''<Key><T>'' + ''' + @RefTable + ''' + ''</T>%''29						 and y.XObjectKey is null30							' end else begin  select @SQLcmd = concat31('			32					update ' , @TableName , '33						set ' , @columnname , ' = null ' , case when dbo.QBM_FGIColumnExistsInSchema(@TableName, 'XDateUpdated') =34 1 and @columnname not like 'X[d,u][a,s][t,e][e,r]updated' then ', XDateUpdated = getutcdate(), XUserUpdated =  ''' + object_name(@@procid) + '''' else35 '' end , ' from ' , @TableName , ' x left outer join ' , @RefTable , ' y  on x.' , @columnname , ' = y.XObjectKey36						where x.' , @columnname , ' like ''<Key><T>'' + '''37 , @RefTable , ''' + ''</T>%''38						 and y.XObjectKey is null39							') end end  end else begin  if @RepairIt = 0 begin select @SQLcmd = '			40				select '''41 + @TableName + ''', ''' + @columnname + ''', ' + str(@MinLen) + ', x.' + @columnname + '42					from ' + @TableName + ' x with (readpast)43					where x.'44 + @columnname + ' like ''<Key><T>'' + ''' + @RefTable + ''' + ''</T>%''45						' end else begin if @MinLen > 0 begin select @SQLcmd = '			46					delete '47 + @TableName + '48						from ' + @TableName + ' x 49						where x.' + @columnname + ' like ''<Key><T>'' + ''' + @RefTable + ''' + ''</T>%''50							' 51end else begin select @SQLcmd = concat('			52					update ' , @TableName + '53						set ' , @columnname , ' = null ' , case when dbo.QBM_FGIColumnExistsInSchema54(@TableName, 'XDateUpdated') = 1 and @columnname not like 'X[d,u][a,s][t,e][e,r]updated' then ', XDateUpdated = getutcdate(), XUserUpdated =  ''' + object_name55(@@procid) + '''' else '' end , ' from ' , @TableName , ' x 56						where x.' , @columnname , ' like ''<Key><T>'' + ''' , @RefTable , ''' + ''</T>%''57							'58) end end  end if @RepairIt = 0 begin if @DebugSwitch > 0 begin print @Sqlcmd end insert into @Results (Ident1 , ident2  , int1  , ObjectKey1 ) exec sp_executeSQL59 @SQLcmd end else begin if @DebugSwitch > 0 begin print @Sqlcmd end exec sp_executeSQL @SQLcmd end delete  @RefTables where UID_SingleGuid  = @RefTable60 end  if @RepairIt = 0 begin select Ident1 as TableName , ident2 as ColumnName , int1 as MinLen , ObjectKey1 as ObjectKeyInvalid from @Results end END 61TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT62 END CATCH ende:  return end 63