Back to OIM Explorer

dbo.QBM_ZCheckInvalidDate

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 1.880 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_PExecuteSQLWithRetry_LLP source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL83 lines
1CREATE PROCEDURE QBM_ZCheckInvalidDate(2  @SlotNumber int,3  @TablePattern varchar(38),4  @Dummy2 varchar(38),5  @DummyGenProcID varchar(38)6)7AS8BEGIN9  DECLARE @table nvarchar(64)10  DECLARE @column nvarchar(64)11  DECLARE @SQLcmd nvarchar(max)12  DECLARE @ElementBuffer QBM_YCursorBuffer13  DECLARE @ElementCount int14  DECLARE @ElementIndex int15  DECLARE @DebugSwitch int = 016  BEGIN TRY17    INSERT INTO @ElementBuffer(Ident1,18    Ident2)19    SELECT20      c.table_name,21      c.column_name22    FROM information_schema.columns c23      WITH(readpast)24    JOIN information_schema.tables t25      WITH(readpast)26      ON t.table_name = c.table_name AND t.table_type = 'base table'27    WHERE28      c.DATA_TYPE = 'datetime' AND c.column_name NOT LIKE 'Xdate%' AND c.IS_NULLABLE = 'YES' AND t.Table_Name LIKE @TablePattern AND t.table_name29    NOT IN(30    SELECT TableName31    FROM QBM_VHeavyLoadTables)32    SELECT @ElementCount = @@ROWCOUNT33    SELECT @ElementIndex = 134    WHILE @ElementIndex <= @ElementCount35    BEGIN36      SELECT37        TOP 1 @table = bu.Ident1,38        @column = bu.Ident239      FROM @ElementBuffer bu40      WHERE41        bu.ElementIndex = @ElementIndex42      SELECT43        @SQLcmd = CONCAT('updat',44        'e ',45        @table,46        '47							set ',48        @column,49        '= NULL  ',50      CASE51        WHEN dbo.QBM_FGIColumnExistsInSchema(@table, 'XDateUpdated') = 1 AND @column NOT LIKE 'X[d,u][a,s][t,e][e,r]updated' THEN52      CONCAT('', '	--3410153												, XDateUpdated = getutcdate()54												, XUserUpdated = ''', object_name(@@procid),55      '''56												')57      ELSE ''58      END,59      ' where ',60      @column,61      ' is not null and isnull(',62      @column,63      ', ''1899-12-30'' ) < ''1899-12-31''')64      IF @DebugSwitch > 065      BEGIN66        print @SQLcmd67      END68      EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd,69        @LockTimeout_ms = DEFAULT,70        @MaxWaitTimeForLock_s = DEFAULT,71        @ProcIDForJournal = @@procid,72        @HandleErrorSilent = 073      SELECT @ElementIndex += 174    END75  END TRY76  BEGIN CATCH77    EXEC QBM_PSessionErrorAdd DEFAULT78    RAISERROR('',79    18,80    1)81      WITH NOWAIT82  END CATCH83END
Open raw exported source
SQL ยท Raw17 lines
1      create   procedure QBM_ZCheckInvalidDate ( @SlotNumber int , @TablePattern varchar(38) , @Dummy2 varchar(38) , @DummyGenProcID varchar(38)2 ) as begin  declare @table nvarchar(64) declare @column nvarchar(64) declare @SQLcmd nvarchar(max) declare @ElementBuffer QBM_YCursorBuffer declare @ElementCount3 int declare @ElementIndex int declare @DebugSwitch int = 0 BEGIN TRY insert into @ElementBuffer (Ident1, Ident2) select c.table_name, c.column_name from4 information_schema.columns c with (readpast) join information_schema.tables t with (readpast) on t.table_name = c.table_name and t.table_type = 'base table'5 where c.DATA_TYPE = 'datetime' and c.column_name not like 'Xdate%' and c.IS_NULLABLE = 'YES' and t.Table_Name like @TablePattern and t.table_name not 6in (select TableName from QBM_VHeavyLoadTables  ) select @ElementCount = @@ROWCOUNT select @ElementIndex = 1 while @ElementIndex <= @ElementCount begin7 select top 1 @table = bu.Ident1 , @column = bu.Ident2 from @ElementBuffer bu where bu.ElementIndex = @ElementIndex select @SQLcmd = CONCAT('updat','e '8, @table , '9							set ' , @column , '= NULL  ' , case when dbo.QBM_FGIColumnExistsInSchema(@table, 'XDateUpdated') = 1 and @column not like 'X[d,u][a,s][t,e][e,r]updated'10 then concat('' , '	--3410111												, XDateUpdated = getutcdate()12												, XUserUpdated = ''', object_name(@@procid) , '''13												') else14 '' end ,' where ', @column, ' is not null and isnull(', @column, ', ''1899-12-30'' ) < ''1899-12-31''' ) if @DebugSwitch > 0 begin print @SQLcmd end exec15 QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd , @LockTimeout_ms = default , @MaxWaitTimeForLock_s = default , @ProcIDForJournal = @@procid , @HandleErrorSilent16 = 0 select @ElementIndex += 1 end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH end 17