Back to OIM Explorer

dbo.ATT_ZAttHelperFillMakeProc

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 6.238 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_FCVStringToIndent source text reference
  • references source dbo.QBM_FGICodeName source text reference
  • references source dbo.QER_FCVRuleGUIDToFuncNameReclc source text reference
  • references source dbo.QBM_PFunctionDrop source text reference
  • references source dbo.QBM_PGICountTablesUsedByCode source text reference
  • references source dbo.QBM_PJournal source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • references source dbo.QBM_PSQLCreate source text reference

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL262 lines
1CREATE PROCEDURE ATT_ZAttHelperFillMakeProc(2  @SlotNumberDummy int,3  @UID_PWODecisionRule varchar(38),4  @Dummy varchar(38),5  @GenProcID varchar(38)6)7AS8BEGIN9  DECLARE @UsageArea char(1)10  DECLARE @FunctionName varchar(30)11  DECLARE @Komplett nvarchar(max)12  DECLARE @ErrorMessage nvarchar(4000)13  DECLARE @ErrorSeverity int14  DECLARE @ErrorState int15  DECLARE @DebugSwitch int = 016  DECLARE @DebugLevel char(1) = 'W'17  DECLARE @Body nvarchar(max) = N ''18  DECLARE @OptionForceOrder nvarchar(1000) = 'option (force order)'19  DECLARE @Muster nvarchar(max)20  DECLARE @Statements QBM_YCursorbuffer21  DECLARE @CountReferencedTables int = 022  DECLARE @LimitOfTableReferences int = 80023  DECLARE @MakeInlineFunction BIT = 124  DECLARE @OpenParenthesis nvarchar(max)25  DECLARE @MyTempElementName varchar(30) =26  LEFT(CONCAT('TST_FTemporaryElement', reverse(dbo.QBM_FGICodeName('V', NEWID()))),27  30)28  SET XACT_ABORT OFF29  BEGIN TRY30    SELECT @FunctionName = dbo.QER_FCVRuleGUIDToFuncNameReclc(@UID_PWODecisionRule)31    SELECT TOP 1 @UsageArea = r.UsageArea32    FROM PWODecisionRule r33    WHERE34      r.UID_PWODecisionRule = @UID_PWODecisionRule35    IF @UsageArea IS NULL36    BEGIN37      IF @DebugSwitch > 038      BEGIN39        print 'dann gibt es diese Regel nicht (mehr), gedropt haben wir, können wir Schluß machen'40      END41      GOTO DropOnly42    END43    IF @UsageArea <> 'A'44    BEGIN45      GOTO endLabel46    END47    INSERT INTO @Statements(ContentFull,48    UID1)49    SELECT50      d.SQLQueryObjectsToRecalc,51      d.UID_PWODecisionRuleRulerDetect52    FROM PWODecisionRuleRulerDetect d53    WHERE54      d.UID_PWODecisionRule = @UID_PWODecisionRule AND d.SQLQueryObjectsToRecalc > ' '55    IF @@rowcount > 056    BEGIN57      SELECT58        @OpenParenthesis = CONCAT('create or alter function dbo.',59        @MyTempElementName,60        '  (@ObjectKeys QBM_YParameterList readonly)61	returns table 62	as63	return (6465	')66      DECLARE @CloseParenthesis nvarchar(max) = '67	)68	'69      EXEC @CountReferencedTables = QBM_PGICountTablesUsedByCode @Statements,70        @CodeName = @MyTempElementName,71        @CodeType = 'function',72        @OpenParenthesis = @OpenParenthesis,73        @CloseParenthesis = '74																						)75																						'76      IF @CountReferencedTables > @LimitOfTableReferences77      BEGIN78        SELECT @MakeInlineFunction = 079      END80      IF @DebugSwitch > 081      BEGIN82        print '@CountReferencedTables' + str(@CountReferencedTables)83      END84    END85    EXEC QBM_PFunctionDrop @MyTempElementName86    IF @MakeInlineFunction = 187    BEGIN88      SELECT89        @muster = 'create function dbo.%FunctionName% ()9091		-- definition for rule 	%UID_PWODecisionRule% , recalculation of helpertable					9293		returns @erg table (UID_AttestationCase varchar(38) collate database_default94												)9596		as97		begin98 99		 declare @ObjectKeys QBM_YParameterList100101			   insert into @ObjectKeys(Parameter1) 102			   select distinct ObjectKey 103					 from QERBufferRecalcDecisionMaker a with (readpast)104					 where a.UID_PWODecisionRule = ''%UID_PWODecisionRule%''105					 and a.ProcessState = 1106107			   insert into @erg(UID_AttestationCase)108			   select z.UID_AttestationCase as UID_AttestationCase109			   from (110111		%body% 112113					 ) as z 114			   group by z.UID_AttestationCase115116			   #option#117118		return119		end120121		'122      SELECT123        @Body = string_agg(CONCAT('select x.UID_AttestationCase', '124			from (125				 -- code from PWODecisionRuleRulerDetect: ',126        d.UID_PWODecisionRuleRulerDetect, '127			', dbo.QBM_FCVStringToIndent(d.SQLQueryObjectsToRecalc, 3), '128				 -- / code from PWODecisionRuleRulerDetect129				) as x	130				'131        ),132        '133			union all134		')135      FROM PWODecisionRuleRulerDetect d136      WHERE137        d.UID_PWODecisionRule = @UID_PWODecisionRule AND d.SQLQueryObjectsToRecalc > ' '138    END139    ELSE140    BEGIN141      SELECT142        @muster = 'create function dbo.%FunctionName% ()143144		-- definition for rule 	%UID_PWODecisionRule% , recalculation of helpertable					145146		returns @erg table (UID_AttestationCase varchar(38) collate database_default147												)148149		as150		begin151 152		 declare @Puffer QBM_YParameterList153		 declare @ObjectKeys QBM_YParameterList154155			   insert into @ObjectKeys(Parameter1) 156			   select distinct ObjectKey 157					 from QERBufferRecalcDecisionMaker a with (readpast)158					 where a.UID_PWODecisionRule = ''%UID_PWODecisionRule%''159					 and a.ProcessState = 1160161			%body% 162163			   insert into @erg(UID_AttestationCase)164			   select z.UID_AttestationCase as UID_AttestationCase165			   from (166167					select p.Parameter1 as UID_AttestationCase168						from @Puffer p169170					 ) as z 171			   group by z.UID_AttestationCase172173			   #option#174175		return176		end177178		'179      SELECT180        @Body = string_agg(CONCAT('insert into @Puffer (Parameter1)181			 -- code from PWODecisionRuleRulerDetect: ',182        d.UID_PWODecisionRuleRulerDetect, '183		', dbo.QBM_FCVStringToIndent(d.SQLQueryObjectsToRecalc, 3), '184185			#option#186187			 -- / code from PWODecisionRuleRulerDetect188			'189        ),190        '191	')192      FROM PWODecisionRuleRulerDetect d193      WHERE194        d.UID_PWODecisionRule = @UID_PWODecisionRule AND d.SQLQueryObjectsToRecalc > ' '195    END196    IF @DebugSwitch > 0197    BEGIN198      print '#####################'199      SELECT @Body print '#####################'200    END201    IF isnull(@Body,202    '') = ''203    BEGIN204      GOTO DropOnly205    END206    SELECT207      @Komplett = replace(replace(replace(replace(replace(@Muster, '%FunctionName%', @FunctionName),208      '%parameters%', '@ObjectKey varchar(138)'), '%Body%', dbo.qbm_fcvstringtoindent(@Body, 3)), '%UID_PWODecisionRule%',209      @UID_PWODecisionRule),210      '#option#',211    CASE212      WHEN @Body LIKE '%' + @OptionForceOrder + '%' THEN213    @OptionForceOrder214    ELSE ''215    END)216    IF @DebugSwitch > 0217    BEGIN218      print @komplett219    END220    EXEC QBM_PSQLCreate @FunctionName,221    'F',222      @komplett,223      @UnComment = 0,224      @unformat = 0225    IF @DebugSwitch = 2226    BEGIN227      BEGIN TRY228        SELECT229          @komplett = 'if exists (select top 1 1 from  dbo.' + @FunctionName + '(''deadbeef-dead-beef-dead-beefdeadbeef'', ''deadbeef-dead-beef-dead-beefdeadbeef'') ) print ''bla'' '230        EXEC sp_executeSQL @komplett231        SELECT232          @ErrorMessage = CONCAT('OK ',233          'proc: ',234          OBJECT_NAME(@@PROCID),235          ' Obj: ',236          @UID_PWODecisionRule)237        EXEC QBM_PJournal @ErrorMessage,238          @@PROCID,239        'D',240          @DebugLevel241      END TRY242      BEGIN CATCH243        EXEC QBM_PSessionErrorAdd DEFAULT244        EXEC QBM_PJournal 'SessionError',245          @@PROCID,246        'E',247          @DebugLevel248      END CATCH249    END250  END TRY251  BEGIN CATCH252    EXEC QBM_PSessionErrorAdd DEFAULT253    RAISERROR('',254    18,255    1)256      WITH NOWAIT257  END CATCH258  endLabel:259  RETURN DropOnly:260  EXEC QBM_PFunctionDrop @FunctionName261  RETURN262END
Open raw exported source
SQL · Raw125 lines
1   create   procedure ATT_ZAttHelperFillMakeProc ( @SlotNumberDummy int , @UID_PWODecisionRule varchar(38) , @Dummy varchar(38) , @GenProcID varchar2(38) ) as begin  declare @UsageArea char(1) declare @FunctionName varchar(30) declare @Komplett nvarchar(max) declare @ErrorMessage nvarchar(4000) declare3 @ErrorSeverity int declare @ErrorState int declare @DebugSwitch int = 0 declare @DebugLevel char(1) = 'W' declare @Body nvarchar(max) = N'' declare @OptionForceOrder4 nvarchar(1000) = 'option (force order)' declare @Muster nvarchar(max) declare @Statements QBM_YCursorbuffer declare @CountReferencedTables int = 0 declare5 @LimitOfTableReferences int = 800 declare @MakeInlineFunction bit = 1 declare @OpenParenthesis nvarchar(max) declare @MyTempElementName varchar(30) = 6left(concat('TST_FTemporaryElement', reverse(dbo.QBM_FGICodeName('V', NEWID()) )), 30) SET XACT_ABORT OFF BEGIN TRY select @FunctionName = dbo.QER_FCVRuleGUIDToFuncNameReclc7(@UID_PWODecisionRule) select top 1 @UsageArea = r.UsageArea from PWODecisionRule r where r.UID_PWODecisionRule = @UID_PWODecisionRule if @UsageArea is8 null begin if @DebugSwitch > 0 begin print 'dann gibt es diese Regel nicht (mehr), gedropt haben wir, können wir Schluß machen' end goto DropOnly end 9if @UsageArea <> 'A' begin goto endLabel end insert into @Statements(ContentFull, UID1) select d.SQLQueryObjectsToRecalc, d.UID_PWODecisionRuleRulerDetect10 from PWODecisionRuleRulerDetect d where d.UID_PWODecisionRule = @UID_PWODecisionRule and d.SQLQueryObjectsToRecalc > ' ' if @@rowcount > 0 begin select11 @OpenParenthesis = concat('create or alter function dbo.',@MyTempElementName , '  (@ObjectKeys QBM_YParameterList readonly)12	returns table 13	as14	return (1516	'17 ) declare @CloseParenthesis nvarchar(max) = '18	)19	' exec @CountReferencedTables = QBM_PGICountTablesUsedByCode @Statements , @CodeName = @MyTempElementName20 , @CodeType = 'function' , @OpenParenthesis = @OpenParenthesis , @CloseParenthesis = '21																						)22																						' if @CountReferencedTables23 > @LimitOfTableReferences begin select @MakeInlineFunction = 0 end if @DebugSwitch > 0 begin print '@CountReferencedTables' + str(@CountReferencedTables24) end end  exec QBM_PFunctionDrop @MyTempElementName if @MakeInlineFunction = 1  begin select @muster = 'create function dbo.%FunctionName% ()2526		-- definition for rule 	%UID_PWODecisionRule% , recalculation of helpertable					2728		returns @erg table (UID_AttestationCase varchar(38) collate database_default29												)3031		as32		begin33 34		 declare @ObjectKeys QBM_YParameterList3536			   insert into @ObjectKeys(Parameter1) 37			   select distinct ObjectKey 38					 from QERBufferRecalcDecisionMaker a with (readpast)39					 where a.UID_PWODecisionRule = ''%UID_PWODecisionRule%''40					 and a.ProcessState = 14142			   insert into @erg(UID_AttestationCase)43			   select z.UID_AttestationCase as UID_AttestationCase44			   from (4546		%body% 4748					 ) as z 49			   group by z.UID_AttestationCase5051			   #option#5253		return54		end5556		'57 select @Body = string_agg(concat( 'select x.UID_AttestationCase' , '58			from (59				 -- code from PWODecisionRuleRulerDetect: ' , d.UID_PWODecisionRuleRulerDetect60 , '61			' , dbo.QBM_FCVStringToIndent(d.SQLQueryObjectsToRecalc, 3) , '62				 -- / code from PWODecisionRuleRulerDetect63				) as x	64				' )  ,  '65			union all66		'67 )  from PWODecisionRuleRulerDetect d where d.UID_PWODecisionRule = @UID_PWODecisionRule and d.SQLQueryObjectsToRecalc > ' ' end else begin  select @muster68 = 'create function dbo.%FunctionName% ()6970		-- definition for rule 	%UID_PWODecisionRule% , recalculation of helpertable					7172		returns @erg table (UID_AttestationCase varchar(38) collate database_default73												)7475		as76		begin77 78		 declare @Puffer QBM_YParameterList79		 declare @ObjectKeys QBM_YParameterList8081			   insert into @ObjectKeys(Parameter1) 82			   select distinct ObjectKey 83					 from QERBufferRecalcDecisionMaker a with (readpast)84					 where a.UID_PWODecisionRule = ''%UID_PWODecisionRule%''85					 and a.ProcessState = 18687			%body% 8889			   insert into @erg(UID_AttestationCase)90			   select z.UID_AttestationCase as UID_AttestationCase91			   from (9293					select p.Parameter1 as UID_AttestationCase94						from @Puffer p9596					 ) as z 97			   group by z.UID_AttestationCase9899			   #option#100101		return102		end103104		'105 select @Body = string_agg(concat( 'insert into @Puffer (Parameter1)106			 -- code from PWODecisionRuleRulerDetect: ' , d.UID_PWODecisionRuleRulerDetect107 , '108		' , dbo.QBM_FCVStringToIndent(d.SQLQueryObjectsToRecalc, 3) , '109110			#option#111112			 -- / code from PWODecisionRuleRulerDetect113			' )  ,  '114	'115 )  from PWODecisionRuleRulerDetect d where d.UID_PWODecisionRule = @UID_PWODecisionRule and d.SQLQueryObjectsToRecalc > ' ' end if @DebugSwitch > 0 begin116 print '#####################' select @Body print '#####################' end if isnull(@Body, '') = '' begin    goto DropOnly end  select @Komplett = 117replace(replace(replace(replace(replace(@Muster , '%FunctionName%', @FunctionName) , '%parameters%', '@ObjectKey varchar(138)') , '%Body%', dbo.qbm_fcvstringtoindent118( @Body, 3)) , '%UID_PWODecisionRule%', @UID_PWODecisionRule) , '#option#',  case when @Body like '%' + @OptionForceOrder + '%' then @OptionForceOrder 119else '' end) if @DebugSwitch > 0 begin print @komplett end exec QBM_PSQLCreate @FunctionName, 'F', @komplett, @UnComment = 0, @unformat = 0 if @DebugSwitch120 = 2 begin begin try select @komplett = 'if exists (select top 1 1 from  dbo.' + @FunctionName + '(''deadbeef-dead-beef-dead-beefdeadbeef'', ''deadbeef-dead-beef-dead-beefdeadbeef'') ) print ''bla'' '121 exec sp_executeSQL @komplett select @ErrorMessage = concat('OK ' , 'proc: ' , OBJECT_NAME(@@PROCID) , ' Obj: ' , @UID_PWODecisionRule ) exec QBM_PJournal122 @ErrorMessage, @@PROCID, 'D', @DebugLevel end try begin catch exec QBM_PSessionErrorAdd default exec QBM_PJournal 'SessionError', @@PROCID, 'E', @DebugLevel123 end catch end END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH endLabel: return DropOnly:  exec QBM_PFunctionDrop124 @FunctionName return end 125