Microsoft.SqlTools.ManagedBatchParser does not know anything about SQL Agent escape macros

[Matteo-T]

When it comes to T-SQL fragments that contain SQL Agent escape macros, the asymmetry between the native BatchParser (used by tools like sqlcmd.exe) and Microsoft.SqlTools.ManagedBatchParser becomes evident.

It would be really nice (the Invoke-Sqlcmd could take advantage of it, for example - and fill another gap it now has when it was moved from the native BatchParser over to the Microsoft.SqlTools.ManagedBatchParser) if we could support them.

Example 1:

SQLCMD.EXE -S (localdb)\MSSQLLocalDB -q "SELECT N'$(ESCAPE_DQUOTE(SRVR))'" -v SRVR="aa""bb"

parses just fine and yields the following:

--------
aa""""bb

(1 rows affected)

Example 2:

SQLCMD.EXE -S (localdb)\MSSQLLocalDB -q "SELECT N'$(ESCAPE_DQUOTE(SRVR))'"

parses just fine and yields the following:

'SRVR' scripting variable not defined.
-------
$(SRVR)

Example 3:

SQLCMD.EXE -S (localdb)\MSSQLLocalDB -q "SELECT N'$(ESCAPE_DQUOTE(SRVR))'" -x

parses just fine and yields the following:

----------------------
$(ESCAPE_DQUOTE(SRVR))

(1 rows affected)

While Example 3 is covered when we add an option to disable the variable substitution/parsing, examples 1 and 2 are just gaps.

From what I see, we would just need to augment the Lexer/Parser to basically consider the stuff after the initial "$(". If we get another "(" while collecting the characters that make up the identifier, it just means that this could be one of the 4 SQL Agent macros. If it is, then we stash it away and continue reading the chars until the "))" and collect the actual variable. The variable would need to be decorated with a possible Agent Macro, so after we call the IVariableResolver.GetVariable() we have another chance to do the escaping.