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 [The Integer] - 1
WHERE [The Integer] - 1 >= @from
ORDER BY [The Integer]
OPTION (MAXRECURSION 0)
See also : http://mibuso.com/blogs/kriki/2009/11/20/nav-integer-table-in-sql-statement/
Regards,Alain KrikilionUse the SEARCH,Luke! || No PM,please use the forum. || May the <SOLVED>-attribute be in your title!
NAV TechDays 2019: 21 & 22 November 2019, Antwerp (Belgium)