Back to OIM Explorer

dbo.QER_PExclusionCheckCircular

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 3.007 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_FGISessionErrorRethrow source text reference
references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

Complete Source

1CREATE PROCEDURE QER_PExclusionCheckCircular(2  @TargetTable varchar(30),3  @HigherColumn varchar(30),4  @LowerColumn varchar(30),5  @exclusions QBM_YParameterList READONLY6)7AS8BEGIN9  DECLARE @SQLCmd nvarchar(max)10  DECLARE @lauf int11  DECLARE @DebugSwitch int = 012  DECLARE @PatternInitial nvarchar(max) = '13select ex.@HigherColumn, ex.@LowerColumn14	from #WorkTable e join @TargetTable ex on e.UID_Excluding = ex.@HigherColumn15union16select ex.@HigherColumn, ex.@LowerColumn17	from #WorkTable e join @TargetTable ex on e.UID_Excluded = ex.@LowerColumn18union19select ex.@HigherColumn, ex.@LowerColumn20	from #WorkTable e join @TargetTable ex on e.UID_Excluded = ex.@HigherColumn21union22select ex.@HigherColumn, ex.@LowerColumn23	from #WorkTable e join @TargetTable ex on e.UID_Excluding = ex.@LowerColumn24'25  DECLARE @PatternCycle nvarchar(max) = '	26	insert into #WorkTable(UID_Excluding, UID_Excluded)27	select x.UID_Excluding, x.UID_Lower28	 from (29			select neu.UID_Excluding as UID_Excluding , ex.@LowerColumn as UID_Lower30				from #WorkTable neu join @TargetTable ex on neu.UID_Excluded = ex.@HigherColumn31			union 32			select ex.@HigherColumn, neu.UID_Excluded as UID_Lower33				from @TargetTable ex join #WorkTable neu on ex.@LowerColumn = neu.UID_Excluding 34			union 35			select ex.UID_Excluding, neu.UID_Excluded as UID_Lower36				from #WorkTable ex join #WorkTable neu on ex.UID_Excluded = neu.UID_Excluding 37			) as x38		where not exists (select top 1 139							from #WorkTable a40							where a.UID_Excluding = x.UID_Excluding41							and a.UID_Excluded = x.UID_Lower42						)43'44  SET XACT_ABORT OFF45  BEGIN TRY46    drop TABLE47    IF EXISTS #WorkTable48    CREATE TABLE #WorkTable(49      UID_Excluding varchar(38) collate database_default,50      UID_Excluded varchar(38) collate database_default51    )52    INSERT INTO #WorkTable(UID_Excluding,53    UID_Excluded54  )55  SELECT56    e.Parameter1,57    e.Parameter258  FROM @exclusions e59  SELECT60    @SQLCmd = replace(replace(replace(@PatternInitial,61    '@TargetTable',62    @TargetTable),63    '@HigherColumn',64    @HigherColumn),65    '@LowerColumn',66    @LowerColumn67    )68INSERT INTO #WorkTable(UID_Excluding,69UID_Excluded70)71EXEC sp_executesql @SQLCmd72IF @DebugSwitch > 073BEGIN74  SELECT *75  FROM #WorkTable76END77SELECT78  @SQLCmd = replace(replace(replace(@PatternCycle,79  '@TargetTable',80  @TargetTable),81  '@HigherColumn',82  @HigherColumn),83  '@LowerColumn',84  @LowerColumn85  )86SELECT @lauf = 187WHILE @lauf > 088BEGIN89  EXEC sp_executesql @SQLCmd90  SELECT @lauf = @@ROWCOUNT91END92IF @DebugSwitch > 093BEGIN94  SELECT *95  FROM #WorkTable w96  WHERE97    w.UID_Excluding = w.UID_Excluded98END99IF EXISTS(100  SELECT TOP 1 1101  FROM #WorkTable w102  WHERE103    w.UID_Excluding = w.UID_Excluded104)105BEGIN106  SET XACT_ABORT107    ON108  RAISERROR('#LDS#Cannot make a definition because given information would create a cycle.|',109  18,110  2111)112  WITH nowait113END114END TRY115BEGIN CATCH116  EXEC QBM_PSessionErrorAdd DEFAULT117  DECLARE @Rethrow varchar(1000118) = dbo.QBM_FGISessionErrorRethrow(119)120RAISERROR(@Rethrow,12118,1221123)124  WITH NOWAIT125END CATCH126endLabel: truncate TABLE #WorkTable127RETURN128END

Open raw exported source

1      create   procedure QER_PExclusionCheckCircular (@TargetTable varchar(30)  , @HigherColumn varchar (30)  , @LowerColumn varchar(30)  , @exclusions2 QBM_YParameterList readonly   ) as begin  declare @SQLCmd nvarchar(max) declare @lauf int declare @DebugSwitch int = 0 declare @PatternInitial nvarchar3(max) = '4select ex.@HigherColumn, ex.@LowerColumn5	from #WorkTable e join @TargetTable ex on e.UID_Excluding = ex.@HigherColumn6union7select ex.@HigherColumn, ex.@LowerColumn8	from #WorkTable e join @TargetTable ex on e.UID_Excluded = ex.@LowerColumn9union10select ex.@HigherColumn, ex.@LowerColumn11	from #WorkTable e join @TargetTable ex on e.UID_Excluded = ex.@HigherColumn12union13select ex.@HigherColumn, ex.@LowerColumn14	from #WorkTable e join @TargetTable ex on e.UID_Excluding = ex.@LowerColumn15'16 declare @PatternCycle nvarchar(max) = '	17	insert into #WorkTable(UID_Excluding, UID_Excluded)18	select x.UID_Excluding, x.UID_Lower19	 from (20			select neu.UID_Excluding as UID_Excluding , ex.@LowerColumn as UID_Lower21				from #WorkTable neu join @TargetTable ex on neu.UID_Excluded = ex.@HigherColumn22			union 23			select ex.@HigherColumn, neu.UID_Excluded as UID_Lower24				from @TargetTable ex join #WorkTable neu on ex.@LowerColumn = neu.UID_Excluding 25			union 26			select ex.UID_Excluding, neu.UID_Excluded as UID_Lower27				from #WorkTable ex join #WorkTable neu on ex.UID_Excluded = neu.UID_Excluding 28			) as x29		where not exists (select top 1 130							from #WorkTable a31							where a.UID_Excluding = x.UID_Excluding32							and a.UID_Excluded = x.UID_Lower33						)34'35 SET XACT_ABORT OFF BEGIN TRY drop table if exists #WorkTable create table #WorkTable (UID_Excluding varchar(38) collate database_default , UID_Excluded36 varchar(38) collate database_default )  insert into #WorkTable(UID_Excluding, UID_Excluded) select e.Parameter1, e.Parameter2 from @exclusions e select37 @SQLCmd = replace(replace(replace(@PatternInitial , '@TargetTable', @TargetTable) , '@HigherColumn', @HigherColumn) , '@LowerColumn', @LowerColumn) insert38 into #WorkTable(UID_Excluding, UID_Excluded) exec sp_executesql @SQLCmd if @DebugSwitch > 0 begin select * from #WorkTable end select @SQLCmd = replace39(replace(replace(@PatternCycle , '@TargetTable', @TargetTable) , '@HigherColumn', @HigherColumn) , '@LowerColumn', @LowerColumn) select @lauf = 1 while40 @lauf > 0 begin exec sp_executesql @SQLCmd select @lauf = @@ROWCOUNT end if @DebugSwitch > 0 begin select * from #WorkTable w where w.UID_Excluding = 41w.UID_Excluded end  if exists (select top 1 1 from #WorkTable w where w.UID_Excluding = w.UID_Excluded ) begin set XACT_ABORT ON  raiserror( '#LDS#Cannot make a definition because given information would create a cycle.|'42, 18, 2) with nowait end END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR43 (@Rethrow, 18, 1) WITH NOWAIT END CATCH endLabel: truncate table #WorkTable return end 44