question

Ok, assuming you don't want to specify the count and do want unique numbers, here is a proc to do it create proc [dbo].[GenerateNumberListToTotal] @start int -- smallest allowable number , @end int -- largest allowable number , @target int -- target number - all selected numbers must total this amount , @iterationTime int=2 -- how long to hunt for the total in each attempt , @runTime int=20 -- how long overall to hunt for a solution as /* Written 2nd August 2013 Mister Magoo Purpose : To select a pseudo-random set of unique numbers from within a limited pool, which SUM to the requested total. Edit: 08/10/13 changed identity to start at 0 instead of 1 Edit: 08/10/13 changed random number generator to be newid() based Edit: 08/10/13 removed redundant code */ set nocount on; -- Create a temporary table to store the results if object_id('tempdb..#numberList') is not null drop table #numberList; -- it uses the identity column as a running total -- and has a primary key on Number to make checks quick create table #numberList(Number int primary key, Total int identity(0,1)); -- set things up to allow us to insert into the identity column set identity_insert #numberList on; -- End temporary table code -- local variables declare @range int -- holds the scope of allowable numbers = @end - @start + 1 , @endtime datetime -- used in the main loop to prevent searches taking too long , @finishtime datetime; -- used to limit the time spent overall searching for a solution -- calculate the scope of the number range set @range = @end - @start + 1; -- calculate when we must stop searching set @finishtime = dateadd(second,@runTime,getdate()) -- Main loop tests to see if a solution has been found -- or if we have run out of time and exits if either is true while isnull((select sum(Number) from #numberList),0) @target and @finishtime > getdate() begin -- now empty the temporary table truncate table #numberList; -- prime the table with zeros insert #numberList(Number, Total) values(0,0); -- calculate when we must abandon this iteration of the search set @endtime = dateadd(second,@iterationTime,getdate()) -- This one statement WHILE loop does all the work of finding a solution -- It will loop until a solution is found or it has run out of time while ISNULL(SCOPE_IDENTITY(), 0) < @target and @endtime > getdate() insert #numberList(Number, Total) select a.number , ISNULL(SCOPE_IDENTITY(),0) + a.number from -- this next line calculates a new pseudo-random number -- within the allowed number range -- but also limited to not be higher than the remaining -- required value - as there is no need to look for a number -- which would take us over the total required ( values( @start + ( abs( checksum( newid() ) ) % @range ) % ( @target - SCOPE_IDENTITY() ) ) ) as a(number) where a.number0 and not exists (select 1 from #numberList as nl where nl.Number = a.number) ; end -- only return a result set if we succeeded in matching the target value if (select sum(Number) from #numberList) = @target select Number, Total from #numberList where Number0 -- ignore the "seed" value order by total; Call it like this: exec GenerateNumberListToTotal @start = 1 -- smallest allowable number , @end = 120 -- largest allowable number , @target = 5000 -- target number - all selected number must total this amount , @iterationTime = 5 -- how long to hunt for the total in each attempt , @runTime = 15 -- how long overall to hunt for a solution

If you want to allow duplicate numbers in the results, then this version will do it create proc GenerateNumberListToTotalWithDupes @start int -- smallest allowable number , @end int -- largest allowable number , @target int -- target number - all selected numbers must total this amount , @iterationTime int=2 -- how long to hunt for the total in each attempt , @runTime int=20 -- how long overall to hunt for a solution as /* Written 2nd August 2013 Mister Magoo Purpose : To select a pseudo-random set of unique numbers from within a limited pool, which SUM to the requested total. Edit: 08/10/13 changed identity to start at 0 instead of 1 Edit: 08/10/13 changed random number generator to be newid() based Edit: 08/10/13 removed redundant code */ set nocount on; -- Create a temporary table to store the results if object_id('tempdb..#numberList') is not null drop table #numberList; -- it uses the identity column as a running total -- and has a primary key on Number to make checks quick create table #numberList(Number int, Total int identity(0,1)); -- set things up to allow us to insert into the identity column set identity_insert #numberList on; -- End temporary table code -- local variables declare @range int -- holds the scope of allowable numbers = @end - @start + 1 , @endtime datetime -- used in the main loop to prevent searches taking too long , @finishtime datetime; -- used to limit the time spent overall searching for a solution -- calculate the scope of the number range set @range = @end - @start + 1; -- calculate when we must stop searching set @finishtime = dateadd(second,@runTime,getdate()) -- Main loop tests to see if a solution has been found -- or if we have run out of time and exits if either is true while isnull((select sum(Number) from #numberList),0) @target and @finishtime > getdate() begin -- now empty the temporary table truncate table #numberList; -- prime the table with zeros insert #numberList(Number, Total) values(0,0); -- calculate when we must abandon this iteration of the search set @endtime = dateadd(second,@iterationTime,getdate()) -- This one statement WHILE loop does all the work of finding a solution -- It will loop until a solution is found or it has run out of time while ISNULL(SCOPE_IDENTITY(), 0) < @target and @endtime > getdate() insert #numberList(Number, Total) select a.number , ISNULL(SCOPE_IDENTITY(),0) + a.number from -- this next line calculates a new pseudo-random number -- within the allowed number range -- but also limited to not be higher than the remaining -- required value - as there is no need to look for a number -- which would take us over the total required ( values( @start + ( abs( checksum( newid() ) ) % @range ) % ( @target - SCOPE_IDENTITY() ) ) ) as a(number) where a.number0 end -- only return a result set if we succeeded in matching the target value if (select sum(Number) from #numberList) = @target select Number, Total from #numberList where Number0 -- ignore the "seed" value order by total; Call it like this exec GenerateNumberListToTotalWithDupes @start = 1 -- smallest allowable number , @end = 120 -- largest allowable number , @target = 5000 -- target number - all selected number must total this amount , @iterationTime = 5 -- how long to hunt for the total in each attempt , @runTime = 15 -- how long overall to hunt for a solution