I try to generate 3 random numbers which sum is 10. It should show all possibilities. It can work in procedure.

For example, it should look like:

exec AAA (3, 10)    
2 2 6    
1 3 6    
3 2 5    
7 1 2

The record of sum can not repeat.

The same question is for 2 random numbers like below:

exec AAA (2, 10)    
5 5   
4 6    
2 8    
3 7

CodePudding user response：

The stored procedure below inputs a 2 or a 3 for the number of random numbers, and also inputs a Total number. The stored procedure then outputs 2 or 3 random numbers that equal the Total. All combinations of numbers are possibilities of the output.

CREATE OR ALTER PROCEDURE GenerateNumbers
@Number INT,
@Total INT
AS

DECLARE @i INT = @Total
DECLARE @R1 INT
DECLARE @R2 INT
DECLARE @R3 INT

IF @Number = 2
BEGIN
    SET @R1 = ABS(CHECKSUM(NEWID()) % (@Total   1)) --0 through @Total
    SET @R2 = @Total - @R1
    SELECT CAST(@R1 AS varchar)   '   '   CAST(@R2 AS varchar)
END
ELSE IF @Number = 3
BEGIN
    SET @R1 = ABS(CHECKSUM(NEWID()) % (@Total   1)) --0 through @Total
    SET @R2 = ABS(CHECKSUM(NEWID()) % (@Total - @R1   1))
    SET @R3 = @Total - @R1 - @R2
    SELECT CAST(@R1 AS varchar)   '   '   CAST(@R2 AS varchar)   '   '   CAST(@R3 AS varchar)
END
GO

EXEC GenerateNumbers 2, 10
EXEC GenerateNumbers 3, 10

CodePudding user response：

Just for fun, here is a brute force approach which generates every possible combination.

Declare @Sum int = 10
Declare @Cnt int = 3  -- up to 6

;with cte as (
      Select Top (@Sum 1) N=-1 Row_Number() Over (Order By (Select NULL)) 
       From  master..spt_values n1
)
Select Distinct 
       SomeValue = concat(left(A1.N,10)
                          ,' ' left(A2.N,10)
                          ,' ' left(A3.N,10)
                          ,' ' left(A4.N,10)
                          ,' ' left(A5.N,10)
                          ,' ' left(A6.N,10)
                         )
 From cte A1
 Left Join cte A2 on 1=1 and @Cnt>=2 -- essentially a conditional CROSS JOIN
 Left Join cte A3 on 1=1 and @Cnt>=3
 Left Join cte A4 on 1=1 and @Cnt>=4
 Left Join cte A5 on 1=1 and @Cnt>=5
 Left Join cte A6 on 1=1 and @Cnt>=6
 Where A1.N
       coalesce(A2.N,0)
       coalesce(A3.N,0)
       coalesce(A4.N,0)
       coalesce(A5.N,0)
       coalesce(A6.N,0)
      = @Sum

CodePudding user response：

Honestly, I don't know why I'm answering this, but I felt like it anyway. I go down a dynamic SQL route, because that's the way I went... So be it. You'll need a Tally Function (I include the definition of mine here), and I assume SQL Server 2017 . No explanation given, just solution here (If you don't understand it, don't use it)

CREATE   FUNCTION [fn].[Tally] (@End bigint, @StartAtOne bit) 
RETURNS table
AS RETURN
    WITH N AS(
        SELECT N
        FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
    Tally AS(
        SELECT 0 AS I
        WHERE @StartAtOne = 0
        UNION ALL
        SELECT TOP (@End)
               ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
        FROM N N1, N N2, N N3, N N4, N N5, N N6, N N7, N N8)
    SELECT I
    FROM Tally;
GO

CREATE OR ALTER PROC dbo.GetAllTheSums @Numbers int, @Target int, @Debug bit = 0 AS
BEGIN

    SET NOCOUNT ON;

    IF @Numbers < 1 
        THROW 75624, N'Value of @Numbers parameter must be greater than 0',16;

    IF @Target < @Numbers
        THROW 75625, N'Value of @Target is impossible for given value of @Numbers.',16;

    DECLARE @SQL nvarchar(MAX),
            @CRLF NCHAR(2) = NCHAR(13)   NCHAR(10);


    SET @SQL = N'SELECT *'   @CRLF  
               N'FROM fn.Tally(@Target-@numbers 1,1) T1'   @CRLF   --We know we always need to first one
               (SELECT STRING_AGG(N'     CROSS APPLY fn.Tally(@Target-@numbers 1,1) '   QUOTENAME(CONCAT(N'T',T.I 1)),@CRLF)
                FROM fn.Tally(@Numbers-1,1) T)   @CRLF  
               N'WHERE '   (SELECT STRING_AGG(QUOTENAME(CONCAT(N'T',T.I))   N'.I','   ')
                            FROM fn.Tally(@Numbers,1) T)   N' = @Target'   @CRLF  
               (SELECT STRING_AGG(N'  AND '   QUOTENAME(CONCAT(N'T',T.I))   N'.I <= '   QUOTENAME(CONCAT(N'T',T.I 1))   N'.I',@CRLF)
                FROM fn.Tally(@Numbers-1,1) T)   N';'

    IF @Debug = 1
        PRINT @SQL;
    EXEC sys.sp_executesql @SQL, N'@Numbers int, @Target int', @Numbers, @Target;
END;
GO

EXEC dbo.GetAllTheSums 3, 10;

EXEC dbo.GetAllTheSums 2, 12;

EXEC dbo.GetAllTheSums 4, 6, 1; 
GO

EXEC dbo.GetAllTheSums 7, 6; 

db<>fiddle

For larger numbers, or more numbers, don't expect this to be fast, it won't be.