NAV integer table in SQL-statement

krikikriki Member, Moderator Posts: 8,922
edited 2009-11-20 in SQL Tips & Tricks
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/
Regards,Alain Krikilion
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!
NAV TechDays 2020: 19 & 20 November 2020, Antwerp (Belgium)
Sign In or Register to comment.