Multipart identifiers in Microsoft.SqlServer.TransactSql.ScriptDom that are not tables

Question

I'm trying to use Microsoft.SqlServer.TransactSql.ScriptDom to check that an expression is a scalar constant.

Here is such an expression:

DATEADD(YEAR, -21, CURRENT_TIMESTAMP)

Here is not such an expression:

DATEADD(YEAR, -21, DateOfBirth)

It is not a constant because it references the column DateOfBirth. How can I determine this?

What I didn't expect -- and why I've run into trouble -- is that Microsoft.SqlServer.TransactSql.ScriptDom thinks that YEAR is a ColumnReferenceExpression.

Answer 1

(too long for comment)

ScriptDom does not compile, just parses and treats all "strange names" as possible column names, e.g. in IF (MAGICNAME = 0) will be detected a "column" named MAGICNAME. If you want more, you have to add more intelligence to this process by yourself.

This can be done by making additional visitor classes to be used as nested parsers. And by storing lists of "known magic words relevant to specific cases". Which in given case may lead to code which:

• catches udf
• checks if it is a one of well known functions
• invokes nested visitor class which understands more about this specific function

In this approach a specific visitor for DATEADD (or all the date handling functions) might have the list of words YEAR, MONTH and so on to change the understanding of first argument from "possible column" to "known static magic word".

Given task can hardly be accomplished in general, for any possible case, however it looks like many cases can be handled correctly. An idea is to implement "duck typing" approach:

• detect expressions which can possibly be scalar and "constant" and take a deeper look on them only
• in deeper look recursively apply this approach to all expression arguments
• if none of them violates your understanding of "scalar constant expression" - then it is one