Using a macro in a SQL statement

In a standard Dynamics 365 for Finance and Operations 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 that can be called with various arguments. Such approaches save development 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 the active vendor records. Then, we will create a class that uses the created macros to display a vendor list.

How to do it...

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

1. Create a Dynamics 365 for Operations Project and create a new macro named VendTableNotBlocked with the following code snippet:

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

2. In the Dynamics 365 Project, create a new runnable class called VendTableMacro with the following code:

       class VendTableMacro 
      {         
        /// <summary> 
        /// Runs the class with the specified arguments. 
        /// </summary> 
        /// <param name = "_args">The specified arguments.</param> 
        public static void main(Args _args) 
       { 
         VendTable   vendTable; 
 
         while select vendTable 
         where #VendTableNotBlocked(vendTable) 
        { 
           info(strFmt( 
           "%1 - %2", 
           vendTable.AccountNum, 
           vendTable.name())); 
        }         
       } 
 
      } 

3. Run the job and check the results, as shown 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 can be used.

Next, we create a job with the select statement. Here, we use the previously created macro in the 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.

Remember that before we start using macros in SQL queries, we should be aware of the following caveats:

• Too much code in a macro might reduce the SQL statement's readability for other developers
• Cross-references do not take into account the code inside the macro
• Changes in the macro will not reflect in the objects where the macro is used until the objects are recompiled