dbo.QER_PPickedItemInsertRandom

1  create   procedure QER_PPickedItemInsertRandom (@UID_QERPickCategory varchar(38) , @TableName varchar(30) , @PickRate float , @WhereClause nvarchar
2(max) = '1=1' ) as begin declare @heute varchar(32) = dbo.QBM_FCVDatetimeToString(getutcdate()) declare @Anteil int declare @Items QBM_YParameterList declare
3 @SQLCmd nvarchar(max) declare @Columns QBM_YParameterList Declare @ColumnString nvarchar(256) declare @DebugSwitch int = 0 SET XACT_ABORT OFF BEGIN TRY
4 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 @Anteil
5 = convert(int, @PickRate * 10000.0) /100 delete QERPickedItem where UID_QERPickCategory = @UID_QERPickCategory select @SQLCmd = CONCAT('
6select y.XObjectKey
7	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 MaxZeile
10			from (
11				select
12					/*', @TableName , '.UID_Person -- nur für debugging
13					, */ '
14, @TableName , '.XObjectKey
15					, dbo.QBM_FCVAnyToHash(CONCAT(', @TableName , '.XObjectKey, ''', @heute ,''')) as HashValue
16					, convert(float, COUNT(*) over()) as GesamtZahl
17					from '
18, @TableName , ' 
19					-- evtl. Einschränkungen
20					where (', @WhereClause , ')
21					-- evtl. Einschränkungen
22				) as x
23			) as y
24 where y.Zeile <= y.MaxZeile
25'
26 ) if @DebugSwitch > 0 begin print @SQLcmd end insert into @Items(Parameter1) exec sp_executesql @SQLcmd exec QER_PPickedItemInsert_b @UID_QERPickCategory
27, @Items END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18
28, 1) WITH NOWAIT END CATCH  endLabel: return end 
29