Inline table valued function

Question

I need to create a table function that produces a parameter up to a specified number in column 1 always starting from 1. In column 2, if column 1 is divisible by 5 it will say 'Div5' otherwise NULL.

So as an example. I specify column 1 will stop at 5 the end result will look as follows;

1 NULL

2 NULL

3 NULL

4 NULL

5 Div5

I can create the function, but I'm not sure how to create the conditional first column, or how to say if column 2 divided by 5 is an integer then 'Div5' if it's a decimal then NULL;

create function MyFunction ()

Returns @Division Table 

(Ind int , 
Div5 varchar(30))

AS
begin
Insert Into @Division (Ind, Div5)

select ???,???

Return;
End;

I hope this gives enough detail?

Thank you :)

Answer 1

This should do the trick:

DECLARE @divisor INT = 10, @limit INT = 100;

WITH
              L0   AS(SELECT 1 AS C UNION ALL SELECT 1 AS O),
              L1   AS(SELECT 1 AS C FROM L0 AS A CROSS JOIN L0 AS B),
              L2   AS(SELECT 1 AS C FROM L1 AS A CROSS JOIN L1 AS B),
              L3   AS(SELECT 1 AS C FROM L2 AS A CROSS JOIN L2 AS B),
              L4   AS(SELECT 1 AS C FROM L3 AS A CROSS JOIN L3 AS B),
              L5   AS(SELECT 1 AS C FROM L4 AS A CROSS JOIN L4 AS B),
              Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS N FROM L5)
    SELECT N, CASE WHEN N % @divisor = 0 THEN 'Div' + CAST(@divisor AS VARCHAR(100)) ELSE NULL END AS Col2  FROM Nums 
    WHERE N <= @limit

The 2 variables determine what number you're looking for the first column to be divisible by, the second for how far you want to go, the next bit is just a CTE to generate the numbers for the first column (numbers tables are really useful for loads of stuff like this). Then it's just selecting all the numbers from the numbers table up to your limit and a case expression to check whether it's divisible by the number you specify (remainder 0) and a bit of string concatenation for the DivX bit.

You should easily be able to integrate this logic into your function.

Answer 2

You are looking for the Modulo operator that basically returns the remainder of a division problem.

DECLARE @SOMETBL TABLE (ROWNUM INT, DIVSTATUS CHAR(4))

INSERT @SOMETBL
(ROWNUM)
SELECT 1
UNION
SELECT 5
UNION
SELECT 2
UNION
SELECT 10

UPDATE @SOMETBL
SET DIVSTATUS = CASE WHEN ROWNUM%5 > 0 THEN NULL ELSE 'DIV5' END 

SELECT * FROM @SOMETBL