dbo.QER_PQERRiskIndexMakeProc

1     create   procedure QER_PQERRiskIndexMakeProc (@uid_DialogColumn varchar(38)) as begin declare @SQLcmd nvarchar(max) declare @header nvarchar
2(max) declare @footer nvarchar(max) declare @Body nvarchar(max) declare @content1 nvarchar(max) declare @content2 nvarchar(max) declare @TableName nvarchar
3(32) declare @Columnname nvarchar(32) declare @UpdateString nvarchar(max) declare @Vardef nvarchar(max) = '
4drop table if exists #DeltaQERRiskIndex
5
6create table #DeltaQERRiskIndex (XObjectKey varchar(138) collate database_default
7									, NewValue float default 0
8									)
9
10'
11 declare @PostUpdate nvarchar(max) declare @procname varchar(128) declare @DebugSwitch int = 0 declare @DebugLevel char(1) = 'W' SET XACT_ABORT OFF BEGIN
12 TRY select @ProcName = dbo.QER_FGIRiskIndexProcname( @uid_dialogcolumn )  select top 1 @TableName = t.TableName , @Columnname = c.ColumnName from DialogColumn
13 c with (readpast) join DialogTable t with (readpast) on c.UID_DialogTable = t.UID_DialogTable where UID_DialogColumn = @uid_DialogColumn  exec QER_PSQRiskIndexGetUpdate
14 @uid_DialogColumn, @UpdateString output select @PostUpdate = concat('
15	select @ret = @@rowcount
16
17	if @ret > 0
18	 begin
19		create index #DeltaQERRiskIndex1 on #DeltaQERRiskIndex (XObjectKey) include (NewValue) 
20
21		update '
22 , @TableName , '
23			set ' , @Columnname , ' = y.NewValue ' , case when dbo.QBM_FGIColumnExistsInSchema(@TableName, 'XDateUpdated') = 1 and @Columnname
24 not like 'X[d,u][a,s][t,e][e,r]updated' then ', XDateUpdated = getutcdate(), XUserUpdated = ''QERRiskIndexCalculation'' ' else '' end ,'
25			from ' , 
26@TableName , ' d join #DeltaQERRiskIndex y on d.XObjectKey = y.XObjectKey
27			', '' , '
28			truncate table #DeltaQERRiskIndex
29
30	 end
31		' ) select @SQLcmd
32 = dbo.QBM_FSQProcedureDef ( @ProcName , '' , concat(@Vardef , @updatestring , @PostUpdate) , concat('Procedure riskindex for Column : ' , @TableName ,
33 '.' , @Columnname ) ) if @DebugSwitch > 0 begin print @SQLcmd end if @UpdateString > ' ' begin exec QBM_PSQLCreate @ProcName, 'P', @SQLcmd, @UnComment
34 = 0  end else begin exec QBM_PProcedureDrop @ProcName end END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END 
35CATCH ende: return end 
36