Query Macros

Ugh ... It seems I can put that kind of macro in a Where Condition field on a web part and it replaces the ##WHERE##, but it seems like I should be able to just put it in the query as well.

In Web Part queries you have to use ##WHERE## and in the where condition field on the web part you can put your macro there. Not sure what version of Kentico you are on, but in 9 on Hotfix 44 (or around there) there was a change that will cause using macros in queries to not work in some scenarios. Typically if you tried to use the ##WHERE## somewhere other then after the actual WHERE in the query.

Without custom code you can do it ONLY using sql code expressions: ##ORDERBY##, ##COLUMNS##, ##TOPN##, ##WHERE##.
Take a look Loading data using custom queries. The standard approach is: you put your macros in web parts properties and they get resolved in the context of the current document.

If you really have such a need - it is possible, but it will require custom coding. You have to modify back-end code of your web part and run macroresolver on the text of your sql query, for instance:

string SQlQueryTextWithMacros= "select * from CMS_User where userName = '{%CurrentUser.UserName@%}'"
SQlQueryTextWithMacros = MacroContext.CurrentResolver.ResolveMacros(SQlQueryTextWithMacros) 
DataSet ds = ConnectionHelper.ExecuteQuery(SQlQueryTextWithMacros, null, QueryTypeEnum.SQLQuery);