NAV integer table in SQL-statement

kriki

If we want to loop on integers in NAV, we can use the integer table and put some filters on it.
The problem is that in SQL, we don't have such a table.

It is possible to create the table with a recursive CTE (Common Table Expression).

If you want, you can also put the select into a stored procedure and use it.

This is the select:

-- recursive CTE to generate numbers like the NAV Integer table

DECLARE @from AS INTEGER;
DECLARE @to AS INTEGER;

SET @from = -100;
SET @to = 10;

WITH numbertable ([The Integer]) AS
(SELECT @to

 UNION ALL
  
 SELECT [The Integer] - 1
 FROM numbertable
 WHERE [The Integer] - 1 >= @from
)
SELECT *
FROM numbertable
ORDER BY [The Integer]
OPTION (MAXRECURSION 0)

See also : http://mibuso.com/blogs/kriki/2009/11/20/nav-integer-table-in-sql-statement/