Using a macro in an SQL statement

In a standard Dynamics AX application, there are macros such as InventDimJoin and InventDimSelect, which are reused numerous times across the application. These macros are actually full or partial X++ SQL queries, which can be called with various arguments. Such approach saves developing time by allowing you to reuse pieces of X++ SQL queries.

In this recipe, we will create a small macro, which holds a single where clause to display only active vendor records. Then we will create a job, which uses the created macro for displaying a vendor list.

How to do it...

Carry out the following steps in order to complete this recipe:

1. 1. Open the AOT, and create a new macro named VendTableNotBlocked with the following code:

   (%1.Blocked == CustVendorBlocked::No)
   

2. 2. In the AOT, create a new job called VendTableMacro with the following code:

   static void VendTableMacro(Args _args)
   {
   VendTable vendTable;
   while select vendTable
   where #VendTableNotBlocked(vendTable)
   {
   info(strFmt(
   "%1 - %2",
   vendTable.AccountNum,
   vendTable.name()));
   }
   }
   

3. 3. Run the job and check the results, as displayed in the following screenshot:

   

How it works...

First, we define a macro that holds the where clause. Normally, the purpose of defining SQL in a macro is to reuse it a number of times in various places. We use %1 as an argument. More arguments could be added here.

Next, we create a job with the select statement. Here, we use the previously created macro in a where clause and pass vendTable as an argument.

The query works like any other query, but the advantage is that the code in the macro can be reused elsewhere.

Note that although using a macro in a SQL statement can reduce the amount of code, too much code in it might decrease the SQL statement's readability for other developers. So keep it balanced.