Back to OIM Explorer

dbo.QBM_PColumnCheckBlobExternal

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 4.085 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_FGISessionContext source text reference
  • references source dbo.QBM_PSessionContextSet source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

Complete Source

SQL160 lines
1CREATE PROCEDURE QBM_PColumnCheckBlobExternal(2  @TableName varchar(38),3  @GenProcID varchar(38)4)5AS6BEGIN7  SET nocount8    ON9  DECLARE @XUser nvarchar(64) = object_name(@@procid)10  DECLARE @Xdate datetime = getutcdate()11  DECLARE @merk TABLE(columnname varchar(30) collate database_default,12  datatype varchar(64) collate database_default,13  mittlereLaenge int,14  externhalten int DEFAULT 0,15  NumDatatype int)16  DECLARE @mittlereLaengeMitBlobs int,17  @AnzahlBlobs int18  DECLARE @SQLcmd nvarchar(max);19  DECLARE @TableBaseRecordLen int20  DECLARE @UID_DialogTable varchar(38)21  DECLARE @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('')22  SET XACT_ABORT OFF23  BEGIN TRY24    IF isnull(@GenProcID,25    '') = ''26    BEGIN27      SELECT @GenProcID = newid()28    END29    SELECT TOP 1 @UID_DialogTable = UID_DialogTable30    FROM DialogTable31      WITH(readpast)32    WHERE33      TableName = @TableName34    EXEC QBM_PSessionContextSet 'GenProcID',35      @GenProcID36    UPDATE dialogcolumn37    SET IsBlobExternal = 0,38    XDateUpdated = @Xdate,39    XUserUpdated = @XUser40    WHERE41      IsBlobExternal IS NULL AND UID_DialogTable = @UID_DialogTable42    IF EXISTS(43      SELECT TOP 1 144      FROM DialogTable45      WHERE46        TableName = @TableName AND tabletype IN('M'))47    BEGIN48      GOTO ende49    END50    IF NOT EXISTS(51      SELECT TOP 1 152      FROM dialogcolumn53      WHERE54        UID_DialogTable = @UID_DialogTable AND datatype IN(10, 7, 6))55    BEGIN56      EXEC QBM_PSessionContextSet 'GenProcID',57        @GenProcID58      UPDATE dialogcolumn59      SET IsBlobExternal = 0,60      XDateUpdated = @Xdate,61      XUserUpdated = @XUser62      WHERE63        IsBlobExternal = 1 AND UID_DialogTable = @UID_DialogTable64      GOTO ende65    END66    INSERT INTO @merk(columnname,67    mittlereLaenge,68    datatype,69    numdatatype)70    SELECT71      dc.ColumnName,72    CASE73      WHEN dc.SchemaDataType LIKE '%text%' OR dc.SchemaDataType = 'image' OR(dc.SchemaDataType IN('varbinary') AND c.CHARACTER_MAXIMUM_LENGTH = -1) THEN74    800075    ELSE dc.AVGDataLenByte76    END,77    dc.SchemaDataType,78    dc.DataType79    FROM information_schema.columns c80    JOIN dialogcolumn dc81      ON c.Table_name = @TableName AND dc.UID_DialogTable = @UID_DialogTable AND c.column_name = dc.columnname82    UPDATE @merk83    SET externhalten = 184    WHERE85      mittlereLaenge > 600086    SELECT TOP 1 @TableBaseRecordLen = BaseRecordLen87    FROM dialogtable88    WHERE89      TableName = @TableName90    IF @TableBaseRecordLen = 091    BEGIN92      UPDATE @merk93      SET externhalten = 094      WHERE95        externhalten > 096      GOTO publizieren97    END98    SELECT @mittlereLaengeMitBlobs = sum(mittlereLaenge)99    FROM @merk100    WHERE101      externhalten = 0102    SELECT @AnzahlBlobs = count(*)103    FROM @merk104    WHERE105      externhalten = 0 AND numdatatype = 10106    IF @AnzahlBlobs = 0107    BEGIN108      SELECT @AnzahlBlobs = 1109    END110    UPDATE @merk111    SET externhalten = 1112    WHERE113      mittlereLaenge > @TableBaseRecordLen AND externhalten = 0 AND numdatatype = 10114    UPDATE @merk115    SET externhalten = 2116    WHERE117      numdatatype = 10 AND @TableBaseRecordLen > 4000 AND @TableBaseRecordLen + mittlereLaenge > 6000 AND externhalten = 0118    UPDATE @merk119    SET externhalten = 3120    FROM @merk m121    WHERE122      m.numdatatype IN(10) AND @TableBaseRecordLen +(@mittlereLaengeMitBlobs - @TableBaseRecordLen) / @AnzahlBlobs + m.mittlereLaenge > 6000 AND123    externhalten = 0 publizieren:124    UPDATE @merk125    SET externhalten = 0126    FROM @merk m127    WHERE128      EXISTS(129    SELECT TOP 1 1130    FROM(131    VALUES('JobRunparameter', 'Valuetemplate'),('DialogJournal', 'MessageString'),('JobParameter', 'Valuetemplate'),132    ('JobQueue', 'ParamIn'),('DialogTaggedItem', 'AlternateKey'),('JobHistory', 'ParamIn'),('DialogWatchProperty',133    'ContentFull'),('JobQueue', 'ParamIn'),('jobtreeparamcoll', 'ParameterValue'),('DialogNotification', 'WhereClause')) AS v(TableName,134    ColumnName)135    WHERE136      v.TableName = @TableName AND v.columnname = m.Columnname)137    EXEC QBM_PSessionContextSet 'GenProcID',138      @GenProcID139    UPDATE dialogcolumn140    SET IsBlobExternal = sign(m.externhalten),141    Xdateupdated = GetUTCDate(),142    XUserUpdated = 'DBScheduler'143    FROM DialogColumn c144    JOIN @merk m145      ON c.UID_DialogTable = @UID_DialogTable AND c.ColumnName = m.Columnname146    WHERE147      c.isBlobExternal <> sign(m.externhalten)148  END TRY149  BEGIN CATCH150    EXEC QBM_PSessionErrorAdd DEFAULT151    RAISERROR('',152    18,153    1)154      WITH NOWAIT155  END CATCH156  ende:157  EXEC QBM_PSessionContextSet 'GenProcID',158    @GenProcID_R159  RETURN160END
Open raw exported source
SQL ยท Raw28 lines
1      create   procedure QBM_PColumnCheckBlobExternal ( @TableName varchar(38) , @GenProcID varchar(38) ) as begin set nocount on declare @XUser2 nvarchar(64) = object_name(@@procid) declare @Xdate datetime = getutcdate() declare @merk table ( columnname varchar(30) collate database_default, datatype3 varchar(64) collate database_default, mittlereLaenge int, externhalten int default 0, NumDatatype int ) declare  @mittlereLaengeMitBlobs int, @AnzahlBlobs4 int DECLARE @SQLcmd nvarchar(max);    declare @TableBaseRecordLen int  declare @UID_DialogTable varchar(38) declare @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext5('') SET XACT_ABORT OFF BEGIN TRY if isnull(@GenProcID, '') = '' begin select @GenProcID = newid() end select top 1 @UID_DialogTable = UID_DialogTable 6from DialogTable with (readpast) where TableName = @TableName exec QBM_PSessionContextSet 'GenProcID', @GenProcID update dialogcolumn set IsBlobExternal7 = 0 , XDateUpdated = @Xdate , XUserUpdated = @XUser where IsBlobExternal is null and UID_DialogTable = @UID_DialogTable if exists (select top 1 1 from8 DialogTable where TableName = @TableName and tabletype in ('M') ) begin  goto ende end if not exists (select top 1 1 from dialogcolumn where UID_DialogTable9 = @UID_DialogTable and datatype in (10, 7, 6) ) begin  exec QBM_PSessionContextSet 'GenProcID', @GenProcID update dialogcolumn set IsBlobExternal = 0 10, XDateUpdated = @Xdate , XUserUpdated = @XUser where IsBlobExternal = 1 and UID_DialogTable = @UID_DialogTable goto ende end insert into @merk( columnname11, mittlereLaenge, datatype, numdatatype) select dc.ColumnName , case when dc.SchemaDataType like '%text%' or dc.SchemaDataType = 'image'  or ( dc.SchemaDataType12 in ( 'varbinary')  and c.CHARACTER_MAXIMUM_LENGTH = -1 ) then 8000 else dc.AVGDataLenByte end , dc.SchemaDataType, dc.DataType from information_schema.columns13 c join dialogcolumn dc on c.Table_name = @TableName and dc.UID_DialogTable = @UID_DialogTable and c.column_name = dc.columnname   update @merk set externhalten14 = 1 where mittlereLaenge > 6000 select top 1 @TableBaseRecordLen = BaseRecordLen from dialogtable where TableName = @TableName if @TableBaseRecordLen 15= 0  begin update @merk set externhalten = 0 where externhalten > 0 goto publizieren end select @mittlereLaengeMitBlobs = sum(mittlereLaenge) from @merk16 where externhalten = 0  select @AnzahlBlobs = count(*) from @merk where externhalten = 0  and numdatatype = 10 if @AnzahlBlobs = 0 begin select @AnzahlBlobs17 = 1 end update @merk set externhalten = 1 where mittlereLaenge > @TableBaseRecordLen and externhalten = 0 and numdatatype = 10 update @merk set externhalten18 = 2 where numdatatype = 10 and @TableBaseRecordLen > 4000 and @TableBaseRecordLen + mittlereLaenge > 6000 and externhalten = 0 update @merk set externhalten19 = 3 from @merk m where m.numdatatype in ( 10 ) and @TableBaseRecordLen  + (@mittlereLaengeMitBlobs - @TableBaseRecordLen) / @AnzahlBlobs  + m.mittlereLaenge20  > 6000 and externhalten = 0  publizieren: update @merk set externhalten = 0 from @merk m where exists (select top 1 1 from  (values ('JobRunparameter'21, 'Valuetemplate') , ('DialogJournal', 'MessageString') , ('JobParameter' , 'Valuetemplate') , ('JobQueue' , 'ParamIn' ) , ('DialogTaggedItem', 'AlternateKey'22) , ('JobHistory', 'ParamIn') , ('DialogWatchProperty', 'ContentFull') , ('JobQueue', 'ParamIn') , ('jobtreeparamcoll', 'ParameterValue') , ('DialogNotification'23, 'WhereClause') ) as v (TableName, ColumnName) where v.TableName = @TableName and v.columnname = m.Columnname ) exec QBM_PSessionContextSet 'GenProcID'24, @GenProcID update dialogcolumn set IsBlobExternal = sign(m.externhalten), Xdateupdated = GetUTCDate(), XUserUpdated = 'DBScheduler' from DialogColumn25 c join @merk m on c.UID_DialogTable = @UID_DialogTable and c.ColumnName = m.Columnname where c.isBlobExternal <> sign(m.externhalten) END TRY BEGIN CATCH26 exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH  ende:  exec QBM_PSessionContextSet 'GenProcID', @GenProcID_R return end27 28