Microsoft Dynamics NAV-SQL Challenge: The Curse of the Cursor
Consultants and users alike are discussing "issues with cursors in NAV on SQL" on the web, even several Microsoft/NAV teams blog about it. But what the heck is such a "cursor" thingy, and what's the problem with it?
Simplified, an SQL cursor is a temporary declared SQL object - similar to a variable - which is used to store a result-set (= multiple) of records. Such a "cursor" could be used to browse through the records, e.g. within a loop.
Keep in mind that our "goode olde" NAV was originally designed for its own proprietary platform - the native C/SIDE Server. This "oldtimer" works on "Index Sequential Access Methods" (ISAM), and this is still shown in the NAV code processing:
- Define a Key (SETCURRENTKEY);
- Apply a Filter (SETRANGE & Co.);
- Retrieve the Result-Set (FINDSET);
- Loop through the records.
Yet, the C/AL programming language does not provide really "relational" commands, like JOIN or CROSS APPLY etc. with SQL Server, hence we're...
