dbo.QER_ZPWOHelperFillMakeProc

1   create   procedure QER_ZPWOHelperFillMakeProc ( @SlotNumberDummy int , @UID_PWODecisionRule varchar(38) , @Dummy varchar(38) , @GenProcID varchar
2(38) ) as begin  declare @UsageArea char(1) declare @FunctionName varchar(30) declare @Komplett nvarchar(max) declare @DebugSwitch int = 0 declare @DebugLevel
3 char(1) = 'W' declare @Body nvarchar(max) = N'' declare @OptionForceOrder nvarchar(1000) = 'option (force order)' declare @Muster nvarchar(max) declare
4 @Statements QBM_YCursorbuffer declare @CountReferencedTables int = 0 declare @LimitOfTableReferences int = 800 declare @MakeInlineFunction bit = 1 declare
5 @OpenParenthesis nvarchar(max) declare @ResetMessage nvarchar(4000) declare @MyTempElementName varchar(30) = left(concat('TST_FTemporaryElement', reverse
6(dbo.QBM_FGICodeName('V', NEWID()) )), 30) BEGIN TRY select @FunctionName = dbo.QER_FCVRuleGUIDToFuncNameReclc(@UID_PWODecisionRule) select top 1 @UsageArea
7 = 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_PWODecisionRuleRulerDetect
9 from PWODecisionRuleRulerDetect d where d.UID_PWODecisionRule = @UID_PWODecisionRule and d.SQLQueryObjectsToRecalc > ' ' if @@rowcount > 0 begin select
10 @OpenParenthesis = concat('create or alter function dbo.',@MyTempElementName,'  (@ObjectKeys QBM_YParameterList readonly)
11	returns table 
12	as
13	return (
14
15	'
16 ) declare @CloseParenthesis nvarchar(max) = '
17	)
18	' exec @CountReferencedTables = QBM_PGICountTablesUsedByCode @Statements , @CodeName = @MyTempElementName
19 , @CodeType = 'function' , @OpenParenthesis = @OpenParenthesis , @CloseParenthesis = '
20																						)
21																						' if @CountReferencedTables
22 > @LimitOfTableReferences begin select @MakeInlineFunction = 0 end if @DebugSwitch > 0 begin print '@CountReferencedTables' + str(@CountReferencedTables
23) end end exec QBM_PFunctionDrop @MyTempElementName  if @MakeInlineFunction = 1  begin select @Muster = 'create function dbo.%FunctionName% ()
24
25	-- definition for rule 	%UID_PWODecisionRule% , recalculation of helpertable					
26
27	returns @erg table (UID_PersonWantsOrg varchar(38) collate database_default
28											, UID_QERWorkingMethod varchar(38) collate database_default
29											, GenProcID  varchar(38) collate database_default
30											, OrderState nvarchar(16)  collate database_default
31											)
32
33	as
34	begin
35 
36	 declare @ObjectKeys QBM_YParameterList
37
38		   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 = 1
43
44		   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 OrderState
46		   from (
47
48	%body% 
49
50				 ) as z 
51		   group by z.UID_PersonWantsOrg, z.UID_QERWorkingMethod, z.GenProcID, z.OrderState              
52
53		   #option#
54	return
55	end
56
57	'
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 PWODecisionRuleRulerDetect
64			) as x	
65			'
66 )  ,  '
67		union all
68	' )  from PWODecisionRuleRulerDetect d where d.UID_PWODecisionRule = @UID_PWODecisionRule and d.SQLQueryObjectsToRecalc > ' ' end
69 else begin  select @Muster = 'create function dbo.%FunctionName% ()
70
71	-- definition for rule 	%UID_PWODecisionRule% , recalculation of helpertable					
72
73	returns @erg table (UID_PersonWantsOrg varchar(38) collate database_default
74											, UID_QERWorkingMethod varchar(38) collate database_default
75											, GenProcID  varchar(38) collate database_default
76											, OrderState nvarchar(16)  collate database_default
77											)
78
79	as
80	begin