dbo.ATT_ZAttHelperFillMakeProc

1   create   procedure ATT_ZAttHelperFillMakeProc ( @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 @ErrorMessage nvarchar(4000) declare
3 @ErrorSeverity int declare @ErrorState int declare @DebugSwitch int = 0 declare @DebugLevel char(1) = 'W' declare @Body nvarchar(max) = N'' declare @OptionForceOrder
4 nvarchar(1000) = 'option (force order)' declare @Muster nvarchar(max) declare @Statements QBM_YCursorbuffer declare @CountReferencedTables int = 0 declare
5 @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_FCVRuleGUIDToFuncNameReclc
7(@UID_PWODecisionRule) select top 1 @UsageArea = r.UsageArea from PWODecisionRule r where r.UID_PWODecisionRule = @UID_PWODecisionRule if @UsageArea is
8 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_PWODecisionRuleRulerDetect
10 from PWODecisionRuleRulerDetect d where d.UID_PWODecisionRule = @UID_PWODecisionRule and d.SQLQueryObjectsToRecalc > ' ' if @@rowcount > 0 begin select
11 @OpenParenthesis = concat('create or alter function dbo.',@MyTempElementName , '  (@ObjectKeys QBM_YParameterList readonly)
12	returns table 
13	as
14	return (
15
16	'
17 ) declare @CloseParenthesis nvarchar(max) = '
18	)
19	' exec @CountReferencedTables = QBM_PGICountTablesUsedByCode @Statements , @CodeName = @MyTempElementName
20 , @CodeType = 'function' , @OpenParenthesis = @OpenParenthesis , @CloseParenthesis = '
21																						)
22																						' if @CountReferencedTables
23 > @LimitOfTableReferences begin select @MakeInlineFunction = 0 end if @DebugSwitch > 0 begin print '@CountReferencedTables' + str(@CountReferencedTables
24) end end  exec QBM_PFunctionDrop @MyTempElementName if @MakeInlineFunction = 1  begin select @muster = 'create function dbo.%FunctionName% ()
25
26		-- definition for rule 	%UID_PWODecisionRule% , recalculation of helpertable					
27
28		returns @erg table (UID_AttestationCase varchar(38) collate database_default
29												)
30
31		as
32		begin
33 
34		 declare @ObjectKeys QBM_YParameterList
35
36			   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 = 1
41
42			   insert into @erg(UID_AttestationCase)
43			   select z.UID_AttestationCase as UID_AttestationCase
44			   from (
45
46		%body% 
47
48					 ) as z 
49			   group by z.UID_AttestationCase
50
51			   #option#
52
53		return
54		end
55
56		'
57 select @Body = string_agg(concat( 'select x.UID_AttestationCase' , '
58			from (
59				 -- code from PWODecisionRuleRulerDetect: ' , d.UID_PWODecisionRuleRulerDetect
60 , '
61			' , dbo.QBM_FCVStringToIndent(d.SQLQueryObjectsToRecalc, 3) , '
62				 -- / code from PWODecisionRuleRulerDetect
63				) as x	
64				' )  ,  '
65			union all
66		'
67 )  from PWODecisionRuleRulerDetect d where d.UID_PWODecisionRule = @UID_PWODecisionRule and d.SQLQueryObjectsToRecalc > ' ' end else begin  select @muster
68 = 'create function dbo.%FunctionName% ()
69
70		-- definition for rule 	%UID_PWODecisionRule% , recalculation of helpertable					
71
72		returns @erg table (UID_AttestationCase varchar(38) collate database_default
73												)
74
75		as
76		begin
77 
78		 declare @Puffer QBM_YParameterList
79		 declare @ObjectKeys QBM_YParameterList
80