dbo.QER_P30907EB9FB8232867B_func

1 create   procedure QER_P30907EB9FB8232867B_func  ( @Statements QBM_YCursorbuffer readonly , @UsageArea char(1) , @FunctionName varchar(30) , @UID_PWODecisionRule
2 varchar(38) , @Komplett nvarchar(max) output  ) as begin declare @OpenParenthesis nvarchar(max) declare @CountReferencedTables int = 0 declare @LimitOfTableReferences
3 int = 800 declare @MakeInlineFunction bit = 1 declare @Muster nvarchar(max) declare @Body nvarchar(max) = N'' declare @DebugSwitch int = 0 declare @DebugLevel
4 char(1) = 'W' declare @MyTempElementName varchar(30) = left(concat('TST_FTemporaryElement', reverse(dbo.QBM_FGICodeName('V', NEWID()) )), 30) select @OpenParenthesis
5 = concat('create function dbo.',@MyTempElementName,'  (' , case @UsageArea when 'I' then '@uid_PersonWantsOrg varchar(38)' Else '@uid_AttestationCase varchar(38)'
6 end , '	, @UID_QERWorkingStep varchar(38) )
7																				returns table 
8																				as
9																				return (
10																				'
11 ) exec @CountReferencedTables = QBM_PGICountTablesUsedByCode @Statements , @CodeName = @MyTempElementName , @CodeType = 'function' , @OpenParenthesis 
12= @OpenParenthesis , @CloseParenthesis = '
13																					)
14																					' if @CountReferencedTables > @LimitOfTableReferences begin select
15 @MakeInlineFunction = 0 end   exec QBM_PFunctionDrop @MyTempElementName if @MakeInlineFunction = 1 begin select @Muster = 'create function dbo.%FunctionName% (
16								%parameters%
17							)
18		returns table
19	-- definition for rule 	%UID_PWODecisionRule%					
20	as
21	return 
22	(
23		select z.UID_Person as UID_Person
24			%ReturnPWOOrigin%
25			, convert(varchar(128), ''decision rule %UID_PWODecisionRule%'')  as SourceInfo
26		from (
27
28	%body%
29
30			) as y join Person z on y.UID_Person = z.UID_Person 
31								and z.IsInActive = 0	
32		group by z.UID_Person					
33	)
34	'
35 end else begin select @Muster = 'create function dbo.%FunctionName% (
36								%parameters%
37							)
38	returns @erg table ( UID_Person varchar(38) collate database_default
39					,  UID_PWORulerOrigin varchar(38) collate database_default
40					, SourceInfo varchar(128) collate database_default
41					)
42	-- definition for rule 	%UID_PWODecisionRule%					
43	as
44	begin
45		declare @Puffer QBM_YParameterList
46
47	%body%
48
49	insert into @erg(UID_Person, UID_PWORulerOrigin, SourceInfo)
50	select z.UID_Person as UID_Person
51		,  max(convert(varchar(38), y.UID_PWORulerOrigin)) as UID_PWORulerOrigin
52		, convert(varchar(128), ''decision rule %UID_PWODecisionRule%'')  as SourceInfo
53	from (
54			select p.Parameter1 as UID_Person, p.Parameter2 as UID_PWORulerOrigin
55				from @Puffer p
56					
57		) as y  join Person z on y.UID_Person = z.UID_Person 
58							and z.IsInActive = 0	
59		group by z.UID_Person					
60
61	 return
62	end
63
64	'
65 end if @MakeInlineFunction = 1 begin select @Body = string_agg(concat( 'select x.UID_Person, x.UID_PWORulerOrigin' , '
66	from (
67		 -- code from PWODecisionRuleRulerDetect: '
68 , d.UID1  , '
69	' , dbo.QBM_FCVStringToIndent(d.ContentFull , 3) , '
70		 -- / code from PWODecisionRuleRulerDetect
71		) as x	
72		' )  ,  '
73	union all
74'
75 )  from @Statements  d  end else begin select @Body = string_agg(concat( 'insert into @Puffer(Parameter1, Parameter2)
76		 -- code from PWODecisionRuleRulerDetect: '
77 , d.UID1  , '
78	' , dbo.QBM_FCVStringToIndent(d.ContentFull , 3) , '
79		 -- / code from PWODecisionRuleRulerDetect
80		' )  ,  '