dql-functions.sql

/* Create a function that will return a table with sum of N numbers. N is input.
Table should have values like:
ColumnName
N
N-1+N
N-1+N-2+N
 
….
Total SUM
*/
-- Run your code to show work for 5 as input.

go

CREATE OR ALTER FUNCTION numbersRepeat (@inputVal INT) 
RETURNS @table1 TABLE(SumColumn INT)
AS
BEGIN
	DECLARE @original INT = @inputVal;
	DECLARE @sum INT = 0;
	WHILE (@original > 0)
	BEGIN
		SET @sum = @sum + @original;
		SET @original = @original - 1;
		INSERT INTO @table1 Values(@sum);
	END
RETURN
END
go
SELECT * FROM numbersRepeat(5); 

GO

-- With recursion and CTE
WITH recusiveFunction (inputNumber, sumValues)
AS
(	
	SELECT inputNumber=CAST (5 as INT), sumValues=CAST(5 as INT)  -- Anchor
	UNION ALL 
	SELECT inputNumber-1, sumValues+inputNumber-1 FROM recusiveFunction  -- recursive part
	WHERE inputNumber > 1	 -- terminating logic
)	
SELECT sumValues as sumValues FROM recusiveFunction