Back to OIM Explorer

dbo.QER_ZPWOHelperFillMakeProc

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 6.777 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_PDBQueueCurrentResetNGen source text reference
  • references source dbo.QBM_PFunctionDrop source text reference
  • references source dbo.QBM_PGICountTablesUsedByCode 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

SQL250 lines
1CREATE PROCEDURE QER_ZPWOHelperFillMakeProc(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 @DebugSwitch int = 013  DECLARE @DebugLevel char(1) = 'W'14  DECLARE @Body nvarchar(max) = N ''15  DECLARE @OptionForceOrder nvarchar(1000) = 'option (force order)'16  DECLARE @Muster nvarchar(max)17  DECLARE @Statements QBM_YCursorbuffer18  DECLARE @CountReferencedTables int = 019  DECLARE @LimitOfTableReferences int = 80020  DECLARE @MakeInlineFunction BIT = 121  DECLARE @OpenParenthesis nvarchar(max)22  DECLARE @ResetMessage nvarchar(4000)23  DECLARE @MyTempElementName varchar(30) =24  LEFT(CONCAT('TST_FTemporaryElement', reverse(dbo.QBM_FGICodeName('V', NEWID()))),25  30)26  BEGIN TRY27    SELECT @FunctionName = dbo.QER_FCVRuleGUIDToFuncNameReclc(@UID_PWODecisionRule)28    SELECT TOP 1 @UsageArea = r.UsageArea29    FROM PWODecisionRule r30    WHERE31      r.UID_PWODecisionRule = @UID_PWODecisionRule32    IF @UsageArea IS NULL33    BEGIN34      IF @DebugSwitch > 035      BEGIN36        print 'dann gibt es diese Regel nicht (mehr), gedropt haben wir, können wir Schluß machen'37      END38      GOTO DropOnly39    END40    IF @UsageArea <> 'I'41    BEGIN42      GOTO endLabel43    END44    INSERT INTO @Statements(ContentFull,45    UID1)46    SELECT47      d.SQLQueryObjectsToRecalc,48      d.UID_PWODecisionRuleRulerDetect49    FROM PWODecisionRuleRulerDetect d50    WHERE51      d.UID_PWODecisionRule = @UID_PWODecisionRule AND d.SQLQueryObjectsToRecalc > ' '52    IF @@rowcount > 053    BEGIN54      SELECT55        @OpenParenthesis = CONCAT('create or alter function dbo.',56        @MyTempElementName,57        '  (@ObjectKeys QBM_YParameterList readonly)58	returns table 59	as60	return (6162	')63      DECLARE @CloseParenthesis nvarchar(max) = '64	)65	'66      EXEC @CountReferencedTables = QBM_PGICountTablesUsedByCode @Statements,67        @CodeName = @MyTempElementName,68        @CodeType = 'function',69        @OpenParenthesis = @OpenParenthesis,70        @CloseParenthesis = '71																						)72																						'73      IF @CountReferencedTables > @LimitOfTableReferences74      BEGIN75        SELECT @MakeInlineFunction = 076      END77      IF @DebugSwitch > 078      BEGIN79        print '@CountReferencedTables' + str(@CountReferencedTables)80      END81    END82    EXEC QBM_PFunctionDrop @MyTempElementName83    IF @MakeInlineFunction = 184    BEGIN85      SELECT86        @Muster = 'create function dbo.%FunctionName% ()8788	-- definition for rule 	%UID_PWODecisionRule% , recalculation of helpertable					8990	returns @erg table (UID_PersonWantsOrg varchar(38) collate database_default91											, UID_QERWorkingMethod varchar(38) collate database_default92											, GenProcID  varchar(38) collate database_default93											, OrderState nvarchar(16)  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_PersonWantsOrg, UID_QERWorkingMethod, GenProcID, OrderState)108		   select z.UID_PersonWantsOrg as UID_PersonWantsOrg, z.UID_QERWorkingMethod as UID_QERWorkingMethod, z.GenProcID as GenProcID, z.OrderState as OrderState109		   from (110111	%body% 112113				 ) as z 114		   group by z.UID_PersonWantsOrg, z.UID_QERWorkingMethod, z.GenProcID, z.OrderState              115116		   #option#117	return118	end119120	'121      SELECT122        @Body = string_agg(CONCAT('select x.UID_PersonWantsOrg, x.UID_QERWorkingMethod, x.GenProcID, x.OrderState',123        '124		from (125			 -- code from PWODecisionRuleRulerDetect: ', d.UID_PWODecisionRuleRulerDetect, '126		', dbo.QBM_FCVStringToIndent(d.SQLQueryObjectsToRecalc127        , 3), '128			 -- / code from PWODecisionRuleRulerDetect129			) as x	130			'),131        '132		union all133	')134      FROM PWODecisionRuleRulerDetect d135      WHERE136        d.UID_PWODecisionRule = @UID_PWODecisionRule AND d.SQLQueryObjectsToRecalc > ' '137    END138    ELSE139    BEGIN140      SELECT141        @Muster = 'create function dbo.%FunctionName% ()142143	-- definition for rule 	%UID_PWODecisionRule% , recalculation of helpertable					144145	returns @erg table (UID_PersonWantsOrg varchar(38) collate database_default146											, UID_QERWorkingMethod varchar(38) collate database_default147											, GenProcID  varchar(38) collate database_default148											, OrderState nvarchar(16)  collate database_default149											)150151	as152	begin153	 declare @Puffer QBM_YParameterList154	 declare @ObjectKeys QBM_YParameterList155156		   insert into @ObjectKeys(Parameter1) 157		   select distinct ObjectKey 158				 from QERBufferRecalcDecisionMaker a with (readpast)159				 where a.UID_PWODecisionRule = ''%UID_PWODecisionRule%''160				 and a.ProcessState = 1161162	%body% 163164		   insert into @erg(UID_PersonWantsOrg, UID_QERWorkingMethod, GenProcID, OrderState)165		   select z.UID_PersonWantsOrg as UID_PersonWantsOrg, z.UID_QERWorkingMethod as UID_QERWorkingMethod, z.GenProcID as GenProcID, z.OrderState as OrderState166		   from (167168			select p.Parameter1 as UID_PersonWantsOrg, p.Parameter2 as UID_QERWorkingMethod, p.Parameter3 as GenProcID, p.ContentShort as OrderState169				from @Puffer p170171				 ) as z 172		   group by z.UID_PersonWantsOrg, z.UID_QERWorkingMethod, z.GenProcID, z.OrderState              173174		   #option#175	return176	end177178	'179      SELECT180        @Body = string_agg(CONCAT('insert into @Puffer (Parameter1, Parameter2, Parameter3, ContentShort)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    BEGIN TRY221      EXEC QBM_PSQLCreate @FunctionName,222      'F',223        @komplett,224        @UnComment = 0,225        @unformat = 0226    END TRY227    BEGIN CATCH228      EXEC QBM_PSessionErrorAdd DEFAULT229      SELECT @ResetMessage = ERROR_MESSAGE()230      IF @@TRANCOUNT > 0231      BEGIN232        ROLLBACK233      END234      EXEC QBM_PDBQueueCurrentResetNGen @SlotNumberDummy,235        @ResetMessage,236        @@PROCID237    END CATCH238  END TRY239  BEGIN CATCH240    EXEC QBM_PSessionErrorAdd DEFAULT241    RAISERROR('',242    18,243    1)244      WITH NOWAIT245  END CATCH246  endLabel:247  RETURN DropOnly:248  EXEC QBM_PFunctionDrop @FunctionName249  RETURN250END
Open raw exported source
SQL · Raw125 lines
1   create   procedure QER_ZPWOHelperFillMakeProc ( @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 @DebugSwitch int = 0 declare @DebugLevel3 char(1) = 'W' declare @Body nvarchar(max) = N'' declare @OptionForceOrder nvarchar(1000) = 'option (force order)' declare @Muster nvarchar(max) declare4 @Statements QBM_YCursorbuffer declare @CountReferencedTables int = 0 declare @LimitOfTableReferences int = 800 declare @MakeInlineFunction bit = 1 declare5 @OpenParenthesis nvarchar(max) declare @ResetMessage nvarchar(4000) declare @MyTempElementName varchar(30) = left(concat('TST_FTemporaryElement', reverse6(dbo.QBM_FGICodeName('V', NEWID()) )), 30) BEGIN TRY select @FunctionName = dbo.QER_FCVRuleGUIDToFuncNameReclc(@UID_PWODecisionRule) select top 1 @UsageArea7 = r.UsageArea from PWODecisionRule r where r.UID_PWODecisionRule = @UID_PWODecisionRule if @UsageArea is null begin if @DebugSwitch > 0 begin print 'dann gibt es diese Regel nicht (mehr), gedropt haben wir, können wir Schluß machen'8 end goto DropOnly end if @UsageArea <> 'I' begin goto endLabel end insert into @Statements(ContentFull, UID1) select d.SQLQueryObjectsToRecalc, d.UID_PWODecisionRuleRulerDetect9 from PWODecisionRuleRulerDetect d where d.UID_PWODecisionRule = @UID_PWODecisionRule and d.SQLQueryObjectsToRecalc > ' ' if @@rowcount > 0 begin select10 @OpenParenthesis = concat('create or alter function dbo.',@MyTempElementName,'  (@ObjectKeys QBM_YParameterList readonly)11	returns table 12	as13	return (1415	'16 ) declare @CloseParenthesis nvarchar(max) = '17	)18	' exec @CountReferencedTables = QBM_PGICountTablesUsedByCode @Statements , @CodeName = @MyTempElementName19 , @CodeType = 'function' , @OpenParenthesis = @OpenParenthesis , @CloseParenthesis = '20																						)21																						' if @CountReferencedTables22 > @LimitOfTableReferences begin select @MakeInlineFunction = 0 end if @DebugSwitch > 0 begin print '@CountReferencedTables' + str(@CountReferencedTables23) end end exec QBM_PFunctionDrop @MyTempElementName  if @MakeInlineFunction = 1  begin select @Muster = 'create function dbo.%FunctionName% ()2425	-- definition for rule 	%UID_PWODecisionRule% , recalculation of helpertable					2627	returns @erg table (UID_PersonWantsOrg varchar(38) collate database_default28											, UID_QERWorkingMethod varchar(38) collate database_default29											, GenProcID  varchar(38) collate database_default30											, OrderState nvarchar(16)  collate database_default31											)3233	as34	begin35 36	 declare @ObjectKeys QBM_YParameterList3738		   insert into @ObjectKeys(Parameter1) 39		   select distinct ObjectKey 40				 from QERBufferRecalcDecisionMaker a with (readpast)41				 where a.UID_PWODecisionRule = ''%UID_PWODecisionRule%''42				 and a.ProcessState = 14344		   insert into @erg(UID_PersonWantsOrg, UID_QERWorkingMethod, GenProcID, OrderState)45		   select z.UID_PersonWantsOrg as UID_PersonWantsOrg, z.UID_QERWorkingMethod as UID_QERWorkingMethod, z.GenProcID as GenProcID, z.OrderState as OrderState46		   from (4748	%body% 4950				 ) as z 51		   group by z.UID_PersonWantsOrg, z.UID_QERWorkingMethod, z.GenProcID, z.OrderState              5253		   #option#54	return55	end5657	'58 select @Body = string_agg(concat( 'select x.UID_PersonWantsOrg, x.UID_QERWorkingMethod, x.GenProcID, x.OrderState' , '59		from (60			 -- code from PWODecisionRuleRulerDetect: '61 , d.UID_PWODecisionRuleRulerDetect , '62		' , dbo.QBM_FCVStringToIndent(d.SQLQueryObjectsToRecalc, 3) , '63			 -- / code from PWODecisionRuleRulerDetect64			) as x	65			'66 )  ,  '67		union all68	' )  from PWODecisionRuleRulerDetect d where d.UID_PWODecisionRule = @UID_PWODecisionRule and d.SQLQueryObjectsToRecalc > ' ' end69 else begin  select @Muster = 'create function dbo.%FunctionName% ()7071	-- definition for rule 	%UID_PWODecisionRule% , recalculation of helpertable					7273	returns @erg table (UID_PersonWantsOrg varchar(38) collate database_default74											, UID_QERWorkingMethod varchar(38) collate database_default75											, GenProcID  varchar(38) collate database_default76											, OrderState nvarchar(16)  collate database_default77											)7879	as80	begin81	 declare @Puffer QBM_YParameterList82	 declare @ObjectKeys QBM_YParameterList8384		   insert into @ObjectKeys(Parameter1) 85		   select distinct ObjectKey 86				 from QERBufferRecalcDecisionMaker a with (readpast)87				 where a.UID_PWODecisionRule = ''%UID_PWODecisionRule%''88				 and a.ProcessState = 18990	%body% 9192		   insert into @erg(UID_PersonWantsOrg, UID_QERWorkingMethod, GenProcID, OrderState)93		   select z.UID_PersonWantsOrg as UID_PersonWantsOrg, z.UID_QERWorkingMethod as UID_QERWorkingMethod, z.GenProcID as GenProcID, z.OrderState as OrderState94		   from (9596			select p.Parameter1 as UID_PersonWantsOrg, p.Parameter2 as UID_QERWorkingMethod, p.Parameter3 as GenProcID, p.ContentShort as OrderState97				from @Puffer p9899				 ) as z 100		   group by z.UID_PersonWantsOrg, z.UID_QERWorkingMethod, z.GenProcID, z.OrderState              101102		   #option#103	return104	end105106	'107 select @Body = string_agg(concat( 'insert into @Puffer (Parameter1, Parameter2, Parameter3, ContentShort)108			 -- code from PWODecisionRuleRulerDetect: '109 , d.UID_PWODecisionRuleRulerDetect , '110		' , dbo.QBM_FCVStringToIndent(d.SQLQueryObjectsToRecalc, 3) , '111112			#option#113114			 -- / code from PWODecisionRuleRulerDetect115			'116 )  ,  '117	' )  from PWODecisionRuleRulerDetect d where d.UID_PWODecisionRule = @UID_PWODecisionRule and d.SQLQueryObjectsToRecalc > ' ' end  if @DebugSwitch118 > 0 begin print '#####################' select @Body print '#####################' end if isnull(@Body, '') = '' begin  goto DropOnly end  select @Komplett119 = replace(replace(replace(replace(replace(@Muster , '%FunctionName%', @FunctionName) , '%parameters%', '@ObjectKey varchar(138)') , '%Body%', dbo.qbm_fcvstringtoindent120( @Body, 3)) , '%UID_PWODecisionRule%', @UID_PWODecisionRule) , '#option#',  case when @Body like '%' + @OptionForceOrder + '%' then @OptionForceOrder 121else '' end) if @DebugSwitch > 0 begin print @komplett end BEGIN TRY exec QBM_PSQLCreate @FunctionName, 'F', @komplett, @UnComment = 0, @unformat = 0 END122 TRY BEGIN CATCH exec QBM_PSessionErrorAdd default select @ResetMessage = ERROR_MESSAGE() if @@TRANCOUNT > 0 begin rollback end  exec QBM_PDBQueueCurrentResetNGen123 @SlotNumberDummy , @ResetMessage , @@PROCID END CATCH END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH124 endLabel: return DropOnly:  exec QBM_PFunctionDrop @FunctionName return end 125