Back to OIM Explorer

dbo.QER_PPickedItemInsertRandom

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 1.802 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_FCVAnyToHash source text reference
  • references source dbo.QBM_FCVDatetimeToString source text reference
  • references source dbo.QBM_FCVFloatToString source text reference
  • references source dbo.QBM_FGISessionErrorRethrow source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • references source dbo.QER_PPickedItemInsert source text reference
  • references source dbo.QER_PPickedItemInsert_b source text reference

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL84 lines
1CREATE PROCEDURE QER_PPickedItemInsertRandom(2  @UID_QERPickCategory varchar(38),3  @TableName varchar(30),4  @PickRate float,5  @WhereClause nvarchar(max) = '1=1'6)7AS8BEGIN9  DECLARE @heute varchar(32) = dbo.QBM_FCVDatetimeToString(getutcdate())10  DECLARE @Anteil int11  DECLARE @Items QBM_YParameterList12  DECLARE @SQLCmd nvarchar(max)13  DECLARE @Columns QBM_YParameterList14  DECLARE @ColumnString nvarchar(256)15  DECLARE @DebugSwitch int = 016  SET XACT_ABORT OFF17  BEGIN TRY18    IF NOT @PickRate BETWEEN 0.0 AND 100.019    BEGIN20      RAISERROR('#LDS#PickRate has to be percentage in range [0..100].|',21      18,22      1)23        WITH nowait24    END25    SELECT26      @Anteil = convert(int,27      @PickRate * 10000.0) /10028    DELETE QERPickedItem29    WHERE30      UID_QERPickCategory = @UID_QERPickCategory31    SELECT32      @SQLCmd = CONCAT('33select y.XObjectKey34	from (35		select x.XObjectKey, ROW_NUMBER() over(order by x.HashValue) as Zeile, x.HashValue, x.GesamtZahl, convert(int, x.GesamtZahl * '36      ,37      dbo.QBM_FCVFloatToString(@PickRate),38      ' / 100.0) as MaxZeile39			from (40				select41					/*',42      @TableName,43      '.UID_Person -- nur für debugging44					, */ ',45      @TableName,46      '.XObjectKey47					, dbo.QBM_FCVAnyToHash(CONCAT(',48      @TableName,49      '.XObjectKey, ''',50      @heute,51      ''')) as HashValue52					, convert(float, COUNT(*) over()) as GesamtZahl53					from ',54      @TableName,55      ' 56					-- evtl. Einschränkungen57					where (',58      @WhereClause,59      ')60					-- evtl. Einschränkungen61				) as x62			) as y63 where y.Zeile <= y.MaxZeile64')65    IF @DebugSwitch > 066    BEGIN67      print @SQLcmd68    END69    INSERT INTO @Items(Parameter1)70    EXEC sp_executesql @SQLcmd71    EXEC QER_PPickedItemInsert_b @UID_QERPickCategory,72      @Items73  END TRY74  BEGIN CATCH75    EXEC QBM_PSessionErrorAdd DEFAULT76    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()77    RAISERROR(@Rethrow,78    18,79    1)80      WITH NOWAIT81  END CATCH82  endLabel:83  RETURN84END
Open raw exported source
SQL · Raw29 lines
1  create   procedure QER_PPickedItemInsertRandom (@UID_QERPickCategory varchar(38) , @TableName varchar(30) , @PickRate float , @WhereClause nvarchar2(max) = '1=1' ) as begin declare @heute varchar(32) = dbo.QBM_FCVDatetimeToString(getutcdate()) declare @Anteil int declare @Items QBM_YParameterList declare3 @SQLCmd nvarchar(max) declare @Columns QBM_YParameterList Declare @ColumnString nvarchar(256) declare @DebugSwitch int = 0 SET XACT_ABORT OFF BEGIN TRY4 if not @PickRate between 0.0 and 100.0 begin raiserror ('#LDS#PickRate has to be percentage in range [0..100].|', 18,1) with nowait end select @Anteil5 = convert(int, @PickRate * 10000.0) /100 delete QERPickedItem where UID_QERPickCategory = @UID_QERPickCategory select @SQLCmd = CONCAT('6select y.XObjectKey7	from (8		select x.XObjectKey, ROW_NUMBER() over(order by x.HashValue) as Zeile, x.HashValue, x.GesamtZahl, convert(int, x.GesamtZahl * '9, dbo.QBM_FCVFloatToString( @PickRate) , ' / 100.0) as MaxZeile10			from (11				select12					/*', @TableName , '.UID_Person -- nur für debugging13					, */ '14, @TableName , '.XObjectKey15					, dbo.QBM_FCVAnyToHash(CONCAT(', @TableName , '.XObjectKey, ''', @heute ,''')) as HashValue16					, convert(float, COUNT(*) over()) as GesamtZahl17					from '18, @TableName , ' 19					-- evtl. Einschränkungen20					where (', @WhereClause , ')21					-- evtl. Einschränkungen22				) as x23			) as y24 where y.Zeile <= y.MaxZeile25'26 ) if @DebugSwitch > 0 begin print @SQLcmd end insert into @Items(Parameter1) exec sp_executesql @SQLcmd exec QER_PPickedItemInsert_b @UID_QERPickCategory27, @Items END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 1828, 1) WITH NOWAIT END CATCH  endLabel: return end 29