Back to OIM Explorer

dbo.QER_PQERRiskIndexMakeProc

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 2.258 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_FGIColumnExists source text reference
  • references source dbo.QBM_FGIColumnExistsInSchema source text reference
  • references source dbo.QBM_FSQProcedureDef source text reference
  • references source dbo.QER_FGIRiskIndexProcname source text reference
  • references source dbo.QBM_PProcedureDrop source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • references source dbo.QBM_PSQLCreate source text reference
  • references source dbo.QER_PSQRiskIndexGetUpdate source text reference

References

Referenced By

Complete Source

SQL103 lines
1CREATE PROCEDURE QER_PQERRiskIndexMakeProc(2  @uid_DialogColumn varchar(38)3)4AS5BEGIN6  DECLARE @SQLcmd nvarchar(max)7  DECLARE @header nvarchar(max)8  DECLARE @footer nvarchar(max)9  DECLARE @Body nvarchar(max)10  DECLARE @content1 nvarchar(max)11  DECLARE @content2 nvarchar(max)12  DECLARE @TableName nvarchar(32)13  DECLARE @Columnname nvarchar(32)14  DECLARE @UpdateString nvarchar(max)15  DECLARE @Vardef nvarchar(max) = '16drop table if exists #DeltaQERRiskIndex1718create table #DeltaQERRiskIndex (XObjectKey varchar(138) collate database_default19									, NewValue float default 020									)2122'23  DECLARE @PostUpdate nvarchar(max)24  DECLARE @procname varchar(128)25  DECLARE @DebugSwitch int = 026  DECLARE @DebugLevel char(1) = 'W'27  SET XACT_ABORT OFF28  BEGIN TRY29    SELECT @ProcName = dbo.QER_FGIRiskIndexProcname(@uid_dialogcolumn)30    SELECT31      TOP 1 @TableName = t.TableName,32      @Columnname = c.ColumnName33    FROM DialogColumn c34      WITH(readpast)35    JOIN DialogTable t36      WITH(readpast)37      ON c.UID_DialogTable = t.UID_DialogTable38    WHERE39      UID_DialogColumn = @uid_DialogColumn40    EXEC QER_PSQRiskIndexGetUpdate @uid_DialogColumn,41      @UpdateString OUTPUT42    SELECT43      @PostUpdate = CONCAT('44	select @ret = @@rowcount4546	if @ret > 047	 begin48		create index #DeltaQERRiskIndex1 on #DeltaQERRiskIndex (XObjectKey) include (NewValue) 4950		update '51      ,52      @TableName,53      '54			set ',55      @Columnname,56      ' = y.NewValue ',57    CASE58      WHEN dbo.QBM_FGIColumnExistsInSchema(@TableName, 'XDateUpdated') = 1 AND @Columnname NOT LIKE 'X[d,u][a,s][t,e][e,r]updated' THEN59    ', XDateUpdated = getutcdate(), XUserUpdated = ''QERRiskIndexCalculation'' '60    ELSE ''61    END,62    '63			from ',64    @TableName,65    ' d join #DeltaQERRiskIndex y on d.XObjectKey = y.XObjectKey66			',67    '',68    '69			truncate table #DeltaQERRiskIndex7071	 end72		')73    SELECT74      @SQLcmd = dbo.QBM_FSQProcedureDef(@ProcName,75      '',76      CONCAT(@Vardef, @updatestring, @PostUpdate),77      CONCAT('Procedure riskindex for Column : ', @TableName, '.', @Columnname))78    IF @DebugSwitch > 079    BEGIN80      print @SQLcmd81    END82    IF @UpdateString > ' '83    BEGIN84      EXEC QBM_PSQLCreate @ProcName,85      'P',86        @SQLcmd,87        @UnComment = 088    END89    ELSE90    BEGIN91      EXEC QBM_PProcedureDrop @ProcName92    END93  END TRY94  BEGIN CATCH95    EXEC QBM_PSessionErrorAdd DEFAULT96    RAISERROR('',97    18,98    1)99      WITH NOWAIT100  END CATCH101  ende:102  RETURN103END
Open raw exported source
SQL ยท Raw36 lines
1     create   procedure QER_PQERRiskIndexMakeProc (@uid_DialogColumn varchar(38)) as begin declare @SQLcmd nvarchar(max) declare @header nvarchar2(max) declare @footer nvarchar(max) declare @Body nvarchar(max) declare @content1 nvarchar(max) declare @content2 nvarchar(max) declare @TableName nvarchar3(32) declare @Columnname nvarchar(32) declare @UpdateString nvarchar(max) declare @Vardef nvarchar(max) = '4drop table if exists #DeltaQERRiskIndex56create table #DeltaQERRiskIndex (XObjectKey varchar(138) collate database_default7									, NewValue float default 08									)910'11 declare @PostUpdate nvarchar(max) declare @procname varchar(128) declare @DebugSwitch int = 0 declare @DebugLevel char(1) = 'W' SET XACT_ABORT OFF BEGIN12 TRY select @ProcName = dbo.QER_FGIRiskIndexProcname( @uid_dialogcolumn )  select top 1 @TableName = t.TableName , @Columnname = c.ColumnName from DialogColumn13 c with (readpast) join DialogTable t with (readpast) on c.UID_DialogTable = t.UID_DialogTable where UID_DialogColumn = @uid_DialogColumn  exec QER_PSQRiskIndexGetUpdate14 @uid_DialogColumn, @UpdateString output select @PostUpdate = concat('15	select @ret = @@rowcount1617	if @ret > 018	 begin19		create index #DeltaQERRiskIndex1 on #DeltaQERRiskIndex (XObjectKey) include (NewValue) 2021		update '22 , @TableName , '23			set ' , @Columnname , ' = y.NewValue ' , case when dbo.QBM_FGIColumnExistsInSchema(@TableName, 'XDateUpdated') = 1 and @Columnname24 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.XObjectKey27			', '' , '28			truncate table #DeltaQERRiskIndex2930	 end31		' ) select @SQLcmd32 = 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, @UnComment34 = 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