Back to OIM Explorer

dbo.QBM_PTableCustomRemove_RU

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 4.058 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_FCVGUIDToModuleOwner source text reference
  • references source dbo.QBM_FGIDBOwner source text reference
  • references source dbo.QBM_FGISessionErrorRethrow source text reference
  • references source dbo.QBM_PExecuteSQLWithRetry_LLP source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL207 lines
1CREATE PROCEDURE QBM_PTableCustomRemove_RU(2  @TableName varchar(30)3)4AS5BEGIN6  DECLARE @UID_DialogTable varchar(38) = NULL7  DECLARE @ObjectKeyDialogTable varchar(138)8  DECLARE @TableType varchar(1)9  DECLARE @Sammler QBM_YCursorBuffer10  DECLARE @Step varchar(30)11  DECLARE @SQLCmd nvarchar(max)12  DECLARE @ErrorMessage nvarchar(4000)13  DECLARE @ChildCmd QBM_YCursorBuffer14  DECLARE @ElementCount int15  DECLARE @ElementIndex int16  SET XACT_ABORT OFF17  BEGIN TRY18    SET nocount19      ON20    SELECT21      TOP 1 @UID_DialogTable = t.UID_DialogTable,22      @ObjectKeyDialogTable = t.XObjectKey,23      @TableType = t.TableType24    FROM DialogTable t25    WHERE26      t.TableName = @TableName27    SELECT @Step = 'Checking ProductionLevel' print @step28    IF EXISTS(29      SELECT TOP 1 130      FROM DialogDatabase db31      WITH(readpast)32    WHERE33      db.IsMainDatabase = 1 AND db.ProductionLevel > 1)34    BEGIN35      INSERT INTO @Sammler(Ident1,36      Bit1,37      LongIdent1)38      SELECT39        @Step,40        1,41        'QBM_PTableCustomRemove_RU is only allowed in Dev and QA environments'42    END43    SELECT @Step = 'Checking QBM-Tables' print @step44    IF @UID_DialogTable IS NULL45    BEGIN46      INSERT INTO @Sammler(Ident1,47      Bit1,48      LongIdent1)49      SELECT50        @Step,51        1,52        'Table not found in DialogTable'53    END54    SELECT @Step = 'Checking Table-Type' print @step55    IF @TableType NOT IN('R',56    'U')57    BEGIN58      INSERT INTO @Sammler(Ident1,59      Bit1,60      LongIdent1)61      SELECT62        @Step,63        1,64        'TableType not in (R, U)'65    END66    SELECT @Step = 'Checking schema' print @step67    IF NOT EXISTS(68      SELECT TOP 1 169      FROM INFORMATION_SCHEMA.TABLES t70      WHERE71        t.TABLE_NAME = @TableName)72    BEGIN73      INSERT INTO @Sammler(Ident1,74      Bit1,75      LongIdent1)76      SELECT77        @Step,78        1,79        'Table not found in schema'80    END81    IF NOT EXISTS(82      SELECT TOP 1 183      FROM INFORMATION_SCHEMA.TABLES t84      WHERE85        t.TABLE_NAME = @TableName)86    BEGIN87      INSERT INTO @Sammler(Ident1,88      Bit1,89      LongIdent1)90      SELECT91        @Step,92        1,93        'Table not found in schema'94    END95    SELECT @Step = 'Checking ownership of table' print @step96    IF dbo.QBM_FCVGUIDToModuleOwner(@UID_DialogTable) <> dbo.QBM_FGIDBOwner()97    BEGIN98      INSERT INTO @Sammler(Ident1,99      Bit1,100      LongIdent1)101      SELECT102        @Step,103        1,104        'You are not the owner of ' + @TableName105    END106    IF EXISTS(107      SELECT TOP 1 1108      FROM @Sammler s109      WHERE110        s.Bit1 = 1)111    BEGIN112      SELECT113        s.Ident1 AS Step,114        s.Bit1 AS IsError,115        s.LongIdent1 AS Remarks,116        isnull(s.ContentFull,117        '') AS RepairStatement118      FROM @Sammler s119      ORDER BY s.ElementIndex120      SELECT121        TOP 1 @ErrorMessage = CONCAT('#LDS#Table will not be dropped. 1st error was {0}.|',122        s.LongIdent1,123        '|')124      FROM @sammler s125      WHERE126        s.Bit1 = 1127      ORDER BY s.ElementIndex128      RAISERROR(@ErrorMessage,129      18,130      1)131        WITH nowait132    END133    ELSE134    BEGIN135      print 'try to drop table'136      SELECT137        s.Ident1 AS Step,138        s.Bit1 AS IsError139      FROM @Sammler s140      ORDER BY s.ElementIndex141    END142    INSERT INTO @ChildCmd(ContentFull)143    SELECT144      DISTINCT CONCAT('delete ',145      r.ChildTable,146      '147			from ',148      r.ChildTable,149      ' c 150			where c.',151      r.ChildColumn,152      ' = ''',153      @UID_DialogTable,154      '''155			')156    FROM QBM_VQBMRelation r157    JOIN sys.tables t158      ON r.ChildTable = t.name159    JOIN sys.columns c160      ON t.object_id = c.object_id AND c.name = r.ChildColumn161    WHERE162      ParentTable = 'dialogtable'163    SELECT @ElementCount = @@rowcount164    BEGIN165      TRANSACTION166      DELETE DialogColumnGroupRight167      FROM DialogColumnGroupRight gr168      JOIN DialogColumn c169        ON gr.UID_DialogColumn = c.UID_DialogColumn170      WHERE171        c.UID_DialogTable = @UID_DialogTable172      DELETE DialogColumn173      FROM DialogColumn c174      WHERE175        c.UID_DialogTable = @UID_DialogTable176      SELECT @ElementIndex = 1177      WHILE @ElementIndex <= @ElementCount178      BEGIN179        SELECT TOP 1 @SQLCmd = bu.ContentFull180        FROM @ChildCmd bu181        WHERE182          bu.ElementIndex = @ElementIndex183        EXEC sp_executesql @sqlcmd184        SELECT @ElementIndex += 1185      END186      DELETE DialogTable187      FROM DialogTable a188      WHERE189        a.UID_DialogTable = @UID_DialogTable190      SELECT @SQLCmd = 'drop view ' + @TableName191      EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @sqlcmd,192        @LockTimeout_ms = DEFAULT,193        @MaxWaitTimeForLock_s = DEFAULT,194        @ProcIDForJournal = DEFAULT,195        @HandleErrorSilent = 0 COMMIT TRANSACTION196    END TRY197    BEGIN CATCH198      EXEC QBM_PSessionErrorAdd DEFAULT ROLLBACK TRANSACTION199      DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()200      RAISERROR(@Rethrow,201      18,202      1)203        WITH NOWAIT204    END CATCH205    endLabel:206    RETURN207  END
Open raw exported source
SQL ยท Raw30 lines
1  create   procedure QBM_PTableCustomRemove_RU ( @TableName varchar(30) ) as begin declare @UID_DialogTable varchar(38) = null declare @ObjectKeyDialogTable2 varchar(138) declare @TableType varchar(1) declare @Sammler QBM_YCursorBuffer declare @Step varchar(30) declare @SQLCmd nvarchar(max) declare @ErrorMessage3 nvarchar(4000) declare @ChildCmd QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int SET XACT_ABORT OFF BEGIN TRY set nocount on select4 top 1 @UID_DialogTable = t.UID_DialogTable , @ObjectKeyDialogTable = t.XObjectKey , @TableType = t.TableType from DialogTable t where t.TableName = @TableName5 select @Step = 'Checking ProductionLevel' print @step if exists (select top 1 1 from DialogDatabase db with (readpast) where db.IsMainDatabase = 1 and6 db.ProductionLevel > 1 ) begin insert into @Sammler (Ident1, Bit1, LongIdent1 ) select @Step, 1, 'QBM_PTableCustomRemove_RU is only allowed in Dev and QA environments'7 end select @Step = 'Checking QBM-Tables' print @step if @UID_DialogTable is null begin insert into @Sammler (Ident1, Bit1, LongIdent1 ) select @Step, 81, 'Table not found in DialogTable' end select @Step = 'Checking Table-Type' print @step if @TableType not in ('R', 'U') begin insert into @Sammler (Ident19, Bit1, LongIdent1 ) select @Step, 1, 'TableType not in (R, U)' end select @Step = 'Checking schema' print @step if not exists (select top 1 1 from INFORMATION_SCHEMA.TABLES10 t where t.TABLE_NAME = @TableName ) begin insert into @Sammler (Ident1, Bit1, LongIdent1 ) select @Step, 1, 'Table not found in schema' end if not exists11 (select top 1 1 from INFORMATION_SCHEMA.TABLES t where t.TABLE_NAME = @TableName ) begin insert into @Sammler (Ident1, Bit1, LongIdent1 ) select @Step12, 1, 'Table not found in schema' end   select @Step = 'Checking ownership of table' print @step if dbo.QBM_FCVGUIDToModuleOwner(@UID_DialogTable) <> dbo.QBM_FGIDBOwner13() begin insert into @Sammler (Ident1, Bit1, LongIdent1 ) select @Step, 1, 'You are not the owner of ' + @TableName end if exists (select top 1 1 from 14@Sammler s where s.Bit1 = 1 ) begin select s.Ident1 as Step , s.Bit1 as IsError , s.LongIdent1 as Remarks , isnull(s.ContentFull, '') as RepairStatement15 from @Sammler s order by s.ElementIndex select top 1 @ErrorMessage = concat('#LDS#Table will not be dropped. 1st error was {0}.|' , s.LongIdent1 , '|'16 ) from @sammler s where s.Bit1 = 1 order by s.ElementIndex raiserror(@ErrorMessage, 18, 1) with nowait end else begin print 'try to drop table' select17 s.Ident1 as Step , s.Bit1 as IsError from @Sammler s order by s.ElementIndex end insert into @ChildCmd(ContentFull) select distinct concat( 'delete ' 18, r.ChildTable , '19			from ' , r.ChildTable , ' c 20			where c.' , r.ChildColumn , ' = ''', @UID_DialogTable, '''21			' ) from QBM_VQBMRelation r join22 sys.tables t on r.ChildTable = t.name join sys.columns c on t.object_id = c.object_id and c.name = r.ChildColumn where ParentTable = 'dialogtable' select23 @ElementCount = @@rowcount begin transaction   delete DialogColumnGroupRight from DialogColumnGroupRight gr join DialogColumn c on gr.UID_DialogColumn24 = c.UID_DialogColumn where c.UID_DialogTable = @UID_DialogTable delete DialogColumn from DialogColumn c where c.UID_DialogTable = @UID_DialogTable  select25 @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top 1 @SQLCmd = bu.ContentFull from @ChildCmd bu where bu.ElementIndex = @ElementIndex26 exec sp_executesql @sqlcmd select @ElementIndex += 1 end delete DialogTable from DialogTable a where a.UID_DialogTable = @UID_DialogTable select @SQLCmd27 = 'drop view ' + @TableName exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @sqlcmd , @LockTimeout_ms = default , @MaxWaitTimeForLock_s = default , 28@ProcIDForJournal = default , @HandleErrorSilent = 0 commit Transaction END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default rollback transaction declare29 @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH endLabel: return end  30