Back to OIM Explorer

dbo.QBM_FGIJobQueueTopJob

Scalar FunctionSQL_SCALAR_FUNCTIONSandbox DB

Scalar Function.

Source: sandbox-db sys.sql_modules

Source size: 4.003 characters

Interpretation

  • Database function. Usually supports views, validation, or calculated predicates; look at referenced-by entries for callers.

Relations

  • No extracted relations.

Typed Edges

  • No typed edges extracted for this source.

References

  • No direct source references extracted.

Referenced By

Complete Source

SQL232 lines
1CREATE FUNCTION dbo.QBM_FGIJobQueueTopJob(2  @uid_tree varchar(38)3) RETURNS varchar(384)5  WITH schemabinding6AS7BEGIN8  DECLARE @uid_s varchar(38)9  DECLARE @uid_e varchar(38)10  DECLARE @uid_j varchar(38) = ''11  DECLARE @ready2Exe nvarchar(16) = N ''12  DECLARE @ready2Exe_S nvarchar(16)13  DECLARE @ready2Exe_E nvarchar(16)14  DECLARE @WasError BIT15  DECLARE @JobQueueShadow dbo.QBM_YJobQueueShadow16  INSERT INTO @JobQueueShadow(UID_Job,17  Ready2Exe,18  UID_JobError,19  UID_JobSuccess,20  IsRootJob,21  WasError,22  IsSplitOnly,23  LenErrorMessage,24  IsRecordModified,25  UID_Tree,26  GenProcID,27  JobChainName)28  SELECT29    isnull(q.UID_Job,30    ''),31    isnull(q.Ready2EXE,32    N ''),33    isnull(q.UID_JobError,34    ''),35    isnull(q.UID_JobSuccess,36    ''),37    q.IsRootJob,38    q.WasError,39    q.IsSplitOnly,40    len(isnull(q.ErrorMessages, '')),41    0,42    @uid_tree,43    @uid_tree,44    @uid_tree45  FROM dbo.JobQueue q46    WITH(nolock)47  WHERE48    q.UID_Tree = @uid_tree49  SELECT50    TOP 1 @uid_j = uid_job,51    @ready2Exe = ready2Exe52  FROM @JobQueueShadow s53  WHERE54    ((uid_job NOT IN(55  SELECT UID_JobError56  FROM @JobQueueShadow s) AND uid_job NOT IN(57  SELECT UID_JobSuccess58  FROM @JobQueueShadow s)) OR(isRootJob = 1))59  IF EXISTS(60    SELECT TOP 1 161    FROM @JobQueueShadow j62  LEFT63  OUTER64    JOIN @JobQueueShadow s65      ON j.UID_JobSuccess = s.UID_Job66    WHERE67      j.UID_JobSuccess > ' ' AND s.UID_Job IS NULL) OR EXISTS(68  SELECT TOP 1 169  FROM @JobQueueShadow j70  LEFT71  OUTER72  JOIN @JobQueueShadow e73    ON j.UID_JobError = e.UID_Job74  WHERE75    j.UID_JobError > ' ' AND e.UID_Job IS NULL)76  BEGIN77    GOTO error78  END79  IF @ready2Exe NOT IN(N 'FINISHED',80  N 'DELETE',81  N 'HISTORY')82  BEGIN83    GOTO schluss84  END85  kreisel:86  IF @uid_j = ''87  BEGIN88    GOTO error89  END90  IF EXISTS(91    SELECT TOP 1 192    FROM @JobQueueShadow a93    WHERE94      a.UID_Job = @uid_j AND a.IsRecordModified = 1)95  BEGIN96    GOTO error97  END98  UPDATE @JobQueueShadow99  SET IsRecordModified = 1100  WHERE101    UID_Job = @uid_j102  SELECT103    @uid_s = s.UID_JobSuccess,104    @uid_e = s.UID_JobError,105    @ready2Exe = s.Ready2Exe,106    @WasError = sign(s.WasError + s.LenErrorMessage * s.IsSplitOnly)107  FROM @JobQueueShadow s108  WHERE109    s.UID_Job = @uid_j110  IF @ready2Exe IN(N 'TRUE',111  N 'LOADED',112  N 'PROCESSING',113  N 'FALSE',114  N 'FROZEN')115  BEGIN116    GOTO schluss117  END118  IF @uid_s > ' '119  BEGIN120    SELECT TOP 1 @ready2Exe_S = s.Ready2Exe121    FROM @JobQueueShadow s122    WHERE123      s.UID_Job = @uid_s124  END125  ELSE126  BEGIN127    SELECT @ready2Exe_S = N ''128  END129  IF @uid_e > ' '130  BEGIN131    SELECT TOP 1 @ready2Exe_E = s.Ready2Exe132    FROM @JobQueueShadow s133    WHERE134      uid_job = @uid_e135  END136  ELSE137  BEGIN138    SELECT @ready2Exe_E = N ''139  END140  IF @ready2Exe_S IN(N 'PROCESSING',141  N 'TRUE',142  N 'FROZEN')143  BEGIN144    SELECT @uid_j = @uid_s145    GOTO schluss146  END147  IF @ready2Exe_E IN(N 'PROCESSING',148  N 'TRUE',149  N 'FROZEN')150  BEGIN151    SELECT @uid_j = @uid_e152    GOTO schluss153  END154  IF @WasError = 1 AND @ready2Exe_E = N 'MISSING' AND @ready2Exe_S IN(N 'LOADED',155  N 'FALSE')156  BEGIN157    SELECT @uid_j = @uid_e158    GOTO schluss159  END160  IF @WasError = 0 AND @ready2Exe_S = N 'MISSING' AND @ready2Exe_E IN(N 'LOADED',161  N 'FALSE')162  BEGIN163    SELECT @uid_j = @uid_s164    GOTO schluss165  END166  IF @ready2Exe_S = N 'FALSE' AND @ready2Exe_E IN(N 'FALSE',167  '') AND @wasError = 0168  BEGIN169    SELECT @uid_j = @uid_s170    GOTO schluss171  END172  IF @ready2Exe_E = N 'FALSE' AND @ready2Exe_S IN(N 'FALSE',173  '') AND @wasError = 1174  BEGIN175    SELECT @uid_j = @uid_e176    GOTO schluss177  END178  IF @ready2Exe IN(N 'FINISHED',179  N 'DELETE',180  N 'HISTORY')181  BEGIN182    IF @ready2Exe_S = N ''183    BEGIN184      SELECT @uid_j = @uid_e185      GOTO kreisel186    END187    IF @ready2Exe_E = N ''188    BEGIN189      SELECT @uid_j = @uid_s190      GOTO kreisel191    END192  END193  IF @ready2Exe_S IN(N 'FINISHED',194  N 'DELETE',195  N 'HISTORY')196  BEGIN197    SELECT @uid_j = @uid_s198    GOTO kreisel199  END200  IF @ready2Exe_E IN(N 'FINISHED',201  N 'DELETE',202  N 'HISTORY')203  BEGIN204    SELECT @uid_j = @uid_e205    GOTO kreisel206  END207  IF @ready2Exe_S = N 'LOADED' AND @ready2Exe_E = N 'LOADED'208  BEGIN209    IF @WasError = 1210    BEGIN211      SELECT @uid_j = @uid_e212    END213    ELSE214    BEGIN215      SELECT @uid_j = @uid_s216    END217    GOTO schluss218  END219  IF @ready2Exe_S = N 'LOADED'220  BEGIN221    SELECT @uid_j = @uid_s222    GOTO schluss223  END224  IF @ready2Exe_E = N 'LOADED'225  BEGIN226    SELECT @uid_j = @uid_e227    GOTO schluss228  END229  GOTO schluss error:230  SELECT @uid_j = '' schluss:231  RETURN(@uid_j)232END
Open raw exported source
SQL ยท Raw27 lines
1     create   function dbo.QBM_FGIJobQueueTopJob (@uid_tree varchar(38)) returns varchar(38)  with schemabinding as begin  declare @uid_s varchar2(38) declare @uid_e varchar(38) declare @uid_j varchar(38) = '' declare @ready2Exe nvarchar(16) = N'' declare @ready2Exe_S nvarchar(16) declare @ready2Exe_E3 nvarchar(16)  declare @WasError bit  declare @JobQueueShadow dbo.QBM_YJobQueueShadow           insert into @JobQueueShadow(UID_Job, Ready2Exe, UID_JobError4, UID_JobSuccess, IsRootJob, WasError, IsSplitOnly , LenErrorMessage , IsRecordModified   , UID_Tree, GenProcID, JobChainName ) select isnull(q.UID_Job5, ''), isnull(q.Ready2EXE, N''), isnull(q.UID_JobError, ''), isnull(q.UID_JobSuccess, ''), q.IsRootJob, q.WasError, q.IsSplitOnly , len(isnull(q.ErrorMessages6, '')) , 0  , @uid_tree, @uid_tree, @uid_tree from dbo.JobQueue q with (nolock) where q.UID_Tree = @uid_tree select top 1 @uid_j = uid_job, @ready2Exe 7= ready2Exe from @JobQueueShadow s  where ((uid_job not in (select UID_JobError from @JobQueueShadow s ) and uid_job not in (select UID_JobSuccess from8 @JobQueueShadow s) ) or (isRootJob = 1) )  if exists (select top 1 1 from @JobQueueShadow j left outer join @JobQueueShadow s on j.UID_JobSuccess = s.UID_Job9 where j.UID_JobSuccess > ' ' and s.UID_Job is null ) or exists (select top 1 1 from @JobQueueShadow j left outer join @JobQueueShadow e on j.UID_JobError10 = e.UID_Job where j.UID_JobError > ' ' and e.UID_Job is null ) begin goto error end     if @ready2Exe not in ( N'FINISHED', N'DELETE', N'HISTORY') begin11  goto schluss end kreisel:  if @uid_j = '' begin goto error end   if exists (select top 1 1 from @JobQueueShadow a where a.UID_Job = @uid_j and a.IsRecordModified12 = 1  ) begin  goto error end   update @JobQueueShadow set IsRecordModified = 1 where UID_Job = @uid_j  select @uid_s = s.UID_JobSuccess , @uid_e = s.UID_JobError13  , @ready2Exe = s.Ready2Exe , @WasError = sign(s.WasError + s.LenErrorMessage * s.IsSplitOnly) from @JobQueueShadow s where s.UID_Job = @uid_j if @ready2Exe14 in ( N'TRUE' , N'LOADED', N'PROCESSING', N'FALSE', N'FROZEN' )  begin goto schluss end  if @uid_s > ' ' begin select top 1 @ready2Exe_S = s.Ready2Exe 15from @JobQueueShadow s where s.UID_Job = @uid_s end else begin select @ready2Exe_S = N'' end if @uid_e > ' ' begin select top 1 @ready2Exe_E = s.Ready2Exe16 from @JobQueueShadow s where uid_job = @uid_e end else begin select @ready2Exe_E = N'' end        if @ready2Exe_S in ( N'PROCESSING', N'TRUE', N'FROZEN'17) begin select @uid_j = @uid_s goto schluss end if @ready2Exe_E in ( N'PROCESSING', N'TRUE', N'FROZEN') begin select @uid_j = @uid_e goto schluss end  18 if @WasError = 1 and @ready2Exe_E = N'MISSING' and @ready2Exe_S in( N'LOADED', N'FALSE') begin select @uid_j = @uid_e goto schluss end  if @WasError =19 0 and @ready2Exe_S = N'MISSING' and @ready2Exe_E in( N'LOADED', N'FALSE') begin select @uid_j = @uid_s goto schluss end  if @ready2Exe_S = N'FALSE' and20 @ready2Exe_E in ( N'FALSE', '') and @wasError = 0 begin select @uid_j = @uid_s goto schluss end if @ready2Exe_E = N'FALSE' and @ready2Exe_S in ( N'FALSE'21, '') and @wasError = 1 begin select @uid_j = @uid_e goto schluss end  if @ready2Exe in ( N'FINISHED', N'DELETE', N'HISTORY') begin if @ready2Exe_S = N''22 begin  select @uid_j = @uid_e goto kreisel end if @ready2Exe_E = N'' begin  select @uid_j = @uid_s goto kreisel end end if @ready2Exe_S in ( N'FINISHED'23, N'DELETE', N'HISTORY') begin select @uid_j = @uid_s goto kreisel end  if @ready2Exe_E in ( N'FINISHED', N'DELETE', N'HISTORY') begin select @uid_j = 24@uid_e goto kreisel end   if @ready2Exe_S = N'LOADED' and @ready2Exe_E = N'LOADED' begin if @WasError = 1 begin select @uid_j = @uid_e end else begin select25 @uid_j = @uid_s end goto schluss end if @ready2Exe_S = N'LOADED' begin select @uid_j = @uid_s goto schluss end if @ready2Exe_E = N'LOADED' begin select26 @uid_j = @uid_e goto schluss end  goto schluss  error: select @uid_j = '' schluss: return (@uid_j)  end 27