Table of Contents

Macros

Macros can be used in Reports and SQL Scripts to dynamically create and execute SQL queries at runtime without using SQL string concatenation to construct them and sp_executesql for execution. Macros are written in C#, which offers great flexibility in how and which macros you want to create.

The query below shows an example of how macros can be combined with standard SQL. The {{ and }} tokens specify the presence of a macro. The macro itself is called “MyRowCount”, and it takes 1 numeric argument.

SELECT {{MyRowCount(100)}} *
FROM [Store_WebAppBasicStore_05052015115318]


The macro implementation itself looks like this:

namespace Local
{
    using System;
    using System.Collections.Generic;
    using System.Threading.Tasks;
    using Profitbase.Invision.Scripting.Dynamic;
    public class MyMacros : MacroExpansionProvider
    {
        public string MyRowCount(int num)
        {
            return $"TOP {num}";
        }
    }
}


So in this simple case, the resulting SQL will be:

SELECT TOP 100 *
FROM [Store_WebAppBasicStore_05052015115318]

See Also

Video