macro expansion for sql server

Initial Design Specification

Overall Purpose / Design Objectives

TSQLMacro will provide a means by which SQL developers can:

Design Methodology

The overall design goal will be accomplised by specifying specific formats for TSQL comments that are to be considered macros. To that end, three types of macros will be provided:

Expansion rules

Multiline macros will be expanded by ending the comment on the first comment line and inserting a start comment on the end comment line.

So the following macro:

	SELECT SomeColumn
	FROM SomeTable

Will be expanded to something like:

/*#IFDEF(ABC) E(8466DA48-31A7-4606-97E7-69124AB21084)#*/
	SELECT SomeColumn
	FROM SomeTable
/*#E(8466DA48-31A7-4606-97E7-69124AB21084) #ENDIF#*/

Note that a GUID is inserted in the start and end comments; this guid is present in order to make breakdown, verification, and management of nested macros easier.

Also note that the macro syntax is quite rigid; a macro that does not meet the exact requirements will simply be ignored by the preprocessor. An IFDEF for ABC, for instance, must follow the format:


No additional whitespace is permitted. A matching, properly formatted ENDIF must be present as well, or no expansion will occur. All macros are to be stringently validated in order to ensure that non-macro comments are not expanded.

Inline expansion is somewhat more complex as it involves parameter substitution as well as comment matching. The example from above, SELECTFROMTABLE, formatted as:

/*#SELECTFROMTABLE('MyTable', 'MyColumn')#*/

Will expand to:

/*#SELECTFROMTABLE('MyTable', 'MyColumn')# X(2BEFAA7D-EDCE-4228-AC6B-F7BD25C16CB6)*/SELECT MyColumn ->
-> FROMMyTable/*#Y(2BEFAA7D-EDCE-4228-AC6B-F7BD25C16CB6)#*/
-> represents a line continuation.

Note again the presence of the GUID for verification purposes -- future versions of TSQLMacro may support nested inline macros, much like macros in other programming languages. This GUID should make addition of that feature quite simple. Also note that the expansion is "stuffed" in; therefore, had the original macro had a comment or some TSQL on the same line before or after, it would have been unaffected. For instance:

FROM ATable /*#SELECTFROMTABLE('MyTable', 'MyColumn')#*/ SELECT * FROM AnotherTable

After expansion, three SELECT statements will be available -- one of the primary TSQLMacro design goals is to affect surrounding code and comment blocks as little as possible.

No macros that are commented out by non-macro comments will be touched by the preprocessor -- they will neither be expanded nor collapsed. So the following will never be touched:

--/*#SELECTFROMTABLE('MyTable', 'MyColumn')#*/

Inline macros using the nested syntax (for nesting within multiline comments) will be converted to standard inline comments for expansion. For instance:

	--#SELECTFROMTABLE('MyTable', 'MyColumn')#

Assuming that both the DEBUG macro class and SELECTFROMTABLE macros are enabled, the expansion of this block might look like:

/*#IFDEF(ABC) E(39D18F9C-7A20-4ABB-87C6-3DEAB085849D)#*/
	/*#SELECTFROMTABLE('MyTable', 'MyColumn')# X(FCC6BDDE-827E-4611-A7B2-2BC9B0108A83)*/SELECT MyColumn ->
-> FROM MyTable/*#Y(FCC6BDDE-827E-4611-A7B2-2BC9B0108A83)#*/
/*#E(39D18F9C-7A20-4ABB-87C6-3DEAB085849D) #ENDIF#*/
-> represents a line continuation.

Preprocessor design considerations

The preprocessor will act in the following order on a routine:

  1. All inline macros found within the routine will be collapsed
  2. All inline macros embedded within multiline macros will be converted into the single-line format
  3. All multiline macros will be collapsed
  4. All multiline macros that are enabled will be expanded
  5. Embedded single-line macros that are within enabled multi-line macros will be converted into the inline comment format
  6. All enabled inline macros will be expanded

Once expansion is completed, the preprocessor will automatically re-apply the routine (using the ALTER syntax). It's assumed that the preprocessor will always be run by users with access to alter routines. If there is a need, an access check can be added to a future version.


TSQLMacro will be designed to be flexible and easily extensible for future functionality. Formatting and requirements are somewhat rigid in order to help ensure that the preprocessor does not incorrectly affect unmarked code or mangle desired formatting.
adam machanic
contact: amachanic@datamanipulation.net