TSQLMacro provides a framework for defining dynamically included TSQL in stored procedures, user-defined functions, views, and triggers (collectively referred to as "routines"). The intention of this framework is to assist developers in the following types of circumstances:
A more detailed examination of these situations can be found in the TSQLMacro Introduction.
This document is valid as of TSQLMacro version 0.5. It will be updated appropriately as future versions change the framework.
Installing TSQLMacro is as simple as loading the install script in Query Analyzer and running it. Note that running the install script multiple times will overwrite any previously defined MacroClasses and Macros.
Defining a MacroClass
MacroClasses are defined using the CreateMacroClass stored procedure, which has two parameters: A MacroClass name (@MacroClass), and a flag indicating whether the MacroClass should be enabled or disabled (@Enabled). Both parameters are required. If a MacroClass already exists with the specified name, it will be updated based on the value of the @Enabled parameter.
To define a MacroClass called "DEBUG" and enable it, the following TSQL would be used:
EXEC CreateMacroClass @MacroName = 'DEBUG', @Enabled = 'Y'
To disable this MacroClass, the following TSQL would be used:
EXEC CreateMacroClass @MacroName = 'DEBUG', @Enabled = 'N'
Currently, there is no support for deleting a MacroClass.
Defining a Multi-Line Macro
The primary function of MacroClasses is to serve as a designator for multi-line macros. Multi-line macros are specially formed TSQL comment blocks that will be automatically uncommented or re-commented by the TSQLMacro preprocessor in order to facilitate dynamic inclusion of code fragments in routines.
The basic syntax for defining a multi-line macro is:
/*#IFDEF(MACRO_CLASS_NAME) TSQL_CODE_HERE #ENDIF#*/
In this example, MACRO_CLASS_NAME represents the name of a MacroClass as defined by CreateMacroClass. TSQL_CODE_HERE represents any valid batch of TSQL within the type of routine the multi-line macro has been created in. For instance, within a stored procedure or trigger this could include variable declarations, one or more queries, calls to other stored procedures, temporary table definnitions, and other constructs. Within a user-defined function, on the other hand, some of these constructs -- such as temporary tables -- are not available; these should not be included in a multi-line macro within a user-defined function, as this will result in an error when the preprocessor attempts to expand the macro.
Expanding a Multi-Line Macro
Multi-line macros will be expanded if:
Multi-line macros will not be expanded -- and expanded macros will be collapsed, if the MACRO_CLASS_NAME is disabled or not defined.
The preprocessor is a stored procedure called MacroProcess. It has a single parameter, the name of the routine to process (@RoutineName).
In order to expand macros for a given routine (in this example, called "MyRoutine"), the following TSQL code would be used:
EXEC MacroProcess @RoutineName = 'MyRoutine'
MacroProcess will automatically uncomment or re-comment as necessary in order to create the correct expansion for the macros contained in the routine. It will then re-apply the routine using the TSQL "ALTER" syntax. It is, therefore, very important that the user running MacroProcess has access to alter the routine! If the user does not have access, an error will occur.
Multi-Line Macro Usage Scenarios
Multi-line macros are intended for situations in which code blocks are appropriate for one or more environments in which the routine must run -- and not appropriate to other environments. This situation traditionally requires one of two solutions:
TSQLMacro multi-line macros can be embedded within routines and left for deployment to any environment. Should the DBA for that environment wish to enable the code in the macro, it can be done automatically by enabling the MacroClass within that environment and processing the routine using MacroProcess.
One example of this is debug code, which should only be active in development or quality assurance environments. When a developer is creating a new stored procedure -- or doing debugging work on an existing stored procedure, she will often embed PRINT statements in order to more easily determine the value of variables at certain points in the procedure's logic flow. These are especially prevalent in procedures with large amount of procedural code (IF/THEN blocks, temporary tables, etc.)
Once the developer has completed working on the stored procedure, this debug code will be commented out or deleted in order to roll the procedure into a production environment. Unfortunately, mistakes are often made and developers miss a line of code. Or, worse, a developer who later needs to go back and do additional debug work will have to re-build the same debug logic that the original developer had implemented in the procedure.
TSQLMacro multi-line macros allow permanent embedding of debug logic within stored procedures. By embedding the logic in macros and enabling or disabling them as appropriate, debug assistance is always present when and if it's needed by a developer. And the preprocessor does the work, so there is a much lower chance of error compared to manual editing.
Defining an Inline Macro
TSQLMacro inline macros are defined using the CreateMacro stored procedure. This stored procedure has five parameters:
All parameters are currently required; this could change in a future version, as there is currently no way to edit a single parameter for an existing macro without inputting all five parameters. If the MacroName passed in already exists, all values passed in for that MacroName will be overwritten; so CreateMacro currently also functions as the means by which to update macros.
Macros live within MacroClasses. This enables developers to control enabled macros at a very broad, or very granular level, depending on the scenario. If a MacroClass is disabled, all macros within it will be ignored. If a MacroClass is enabled, the state of each macro within that class will be used to determine whether or not to expand the macro.
Macro parameters are #-delimited tokens, comprised wholly of alphanumeric and underscore characters. These parameters can represent any scalar value, variable name, or collection of scalar values (e.g. a list of substrings within a string) available at the time the preprocessor is running -- but it's important to remember that these values will not be evaluated! A macro is not a function, but rather a compact way to represent larger amounts of code.
The expansion text for a macro can contain any valid TSQL syntax for the routine type in which the macro is embedded -- again, like with multi-line macros, inappropriately embedding invalid TSQL will only result in an error. Currently, it's important that the expansion text should contain no carriage returns. Inline macros will expand to stay on the same line, in order to preserve formatting of surrounding lines of code. Support for multi-line expansion of inline macros is currently being researched for a future version of TSQLMacro.
Macro Expansion Text
In addition to any valid TSQL syntax, macro expansion text can also contain instances of the macro's parameters. These will be directly substituted. So, for instance, if a macro has a parameter, "#PARAM#" and the expansion text for the macro is "THIS IS #PARAM#", if the macro is used with an input "ABC" the final value of the macro will be "THIS IS ABC". Therein lies the power of macros -- generic code blocks that require only simple changes can be defined as macros and more easily embedded in a large number of routines.
Also available within macros is a non-declared parameter called the "magic" parameter. This parameter is denoted by "#!!!#", and expands to a random numeric string between 1 and 999999. This is included so that macros can handle their own setup and breakdown. For instance, if a macro were defined and included a variable declaration in its expansion text (e.g. "DECLARE @SomeVariable INT"), that macro would cause an error if it were used more than once in the same routine. The "magic" parameter avoids this problem by randomizing the variable name. So the following text:
"DECLARE @SomeVariable#!!!# INT SET @SomeVariable#!!!# = 0"
Expands to something like:
"DECLARE @SomeVariable132256 INT SET @SomeVariable132256 = 0"
It is therefore unlikely that a variable name collision will occur as a result of using the same macro multiple times in the same routine.
A very simple example of a macro that could be defined is selecting a certain column from a certain table. This macro might be called "SELECT_TABLE_COLUMN" and might have two parameters, "#TABLE_NAME#" and "#COLUMN_NAME#". This macro would expand to "SELECT #COLUMN_NAME# FROM #TABLE_NAME#".
If this macro were in the "DEBUG" MacroClass, it could be created with the CreateMacro stored procedure, using the following TSQL:
EXEC CreateMacro @MacroClass = 'DEBUG', @MacroName = 'SELECT_TABLE_COLUMN', @MacroParams = '#TABLE_NAME#, #COLUMN_NAME#', @MacroExpansion = 'SELECT #COLUMN_NAME# FROM #TABLE_NAME#', @Enabled = 'Y'
Once an inline macro has been created, it can be embedded in a routine using the following syntax:
MACRO_NAME is the name of the macro as defined by the @MacroName parameter. ARGUMENT_LIST is a list of arguments corresponding to the parameters defined in the @MacroParams parameter of CreateMacro. For instance, to embed the SELECT_TABLE_COLUMN macro in a routine such that when expanded the "Price" column will be selected from the "Products" table, the following code would be used:
Note that no datatypes are defined for macro parameters, so the following would also be valid:
If expanded, this macro would select the number 123 once for every row in the Products table.
Macro Parameter Caveats
There are a few other points to note about macro parameters:
First of all, they are not required. A parameter-less macro can be defined by submitting a blank string as the argument to @MacroParams when calling CreateMacro. This macro can then be called using a blank parameter list:
Second, parameters defined in the @MacroParams argument and parameters embedded in the @MacroExpansion text are not currently validated against each other. Therefore, a macro could be defined with a single parameter: "#ABC#" and its expansion text could be set to "#DEF#". This would be a valid macro, and would expand to "#DEF#" every time it was used. Parameter validation is being researched for a future version.
Finally, the macro preprocessor currently allows macros to be called with less or more arguments than there are parameters defined. For instance, a macro could be defined with parameters "#X#, #Y#, #Z#", and valid inputs to that macro would include "1, 2", "1, 2, 3", or "1, 2, 3, 4", among others. These would behave in the following way during the substitution phase: "1, 2" would map to "#X#" and "#Y#". 1 would replace #X# and 2 would replace #Y#. #Z# would be ignored if it were included in the expansion text. "1, 2, 3" would use all three parameters as expected. And in the case of "1, 2, 3, 4", the 4 would simply be ignored. This feature is under review for future versions of the preprocessor, but it does provide a certain degree of flexibility that may be desirable.
Embedded Inline Macros
A second syntax also exists for defining inline macros. In TSQL, nesting multiline comment blocks (/* and */) is not supported. Given the architecture for TSQLMacro multiline and inline macros, it would be impossible to define an inline macro nested within a multiline macro as both macro types utilize multiline-style comment blocks. Therefore, a second inline macro syntax was devised, for use only within multiline macros. The syntax is:
/*#IFDEF(MACRO_CLASS_NAME) --#MACRO_NAME(ARGUMENT_LIST)# #ENDIF#*/
In this case, MACRO_CLASS_NAME is the name of any MacroClass -- the outer MacroClass does not need to be the MacroClass of which MACRO_NAME is a member. MACRO_NAME is any inline macro as defined by CreateMacro. Upon expansion, this inline macro will be expanded like other inline macros -- but only if the outer multiline macro is enabled and expanded.
Expanding Inline Macros
Inline macros, just like multi-line macros, are expanded using the MacroProcess stored procedure, using the routine name as an argument.
Inline macros will not be expanded, even if enabled, if:
Macros will only be expanded if both they are enabled and their parent MacroClass is enabled.
Macros can be expanded to up to 2000 characters, if the substitution text is that long. Users should be wary of the 4000 character line limit currently supported by the TSQLMacro framework. Truncation (and errors) will occur if macros get too large.
TSQLMacro inline macros are intended for centralizing commonly used code blocks across a database.
Two simple examples are common error handling code and a row-level security scheme.
For error handling within transactions that update multiple tables, numerous stored procedures in a database will often follow the same pattern, similar to the following:
CREATE PROCEDURE UpdateData AS BEGIN SET NOCOUNT ON DECLARE @Error INT BEGIN TRANSACTION UPDATE Tbl ... SET @Error = @@ERROR IF @Error <> 0 BEGIN ROLLBACK GOTO HandleErrors END UPDATE Tbl2 ... SET @Error = @@ERROR IF @Error <> 0 BEGIN ROLLBACK GOTO HandleErrors END ... COMMIT TRANSACTION IF 1=0 BEGIN HandleErrors: --Report error here PRINT 'Error!' END END
Note in this example that the error checking blocks, which occur after every statement in the transaction (due to the reset of @@ERROR after every statement) are exactly the same. And in most shops, the format of this error handling block is a coding standard -- so it will be the same in every stored procedure in the system.
These blocks could each be replaced by an inline macro, defined as follows:
EXEC CreateMacroClass @MacroClass = 'ERROR_HANDLING', @Enabled = 'Y' GO DECLARE @ExpansionText NVARCHAR(1000) SET @ExpansionText = 'SET @Error = @@ERROR ' + 'IF @Error <> 0 ' + 'BEGIN ' + 'ROLLBACK ' + 'GOTO HandleErrors ' + 'END ' EXEC CreateMacro @MacroClass = 'ERROR_HANDLING', @MacroName = 'COMMON_ERROR_BLOCK', @MacroParams = '', @MacroExpansion = @ExpansionText, @Enabled = 'Y' GO
Note that in this case a variable, @ExpansionText, was used to define the expansion text, and that the text has no embedded line-breaks. This is due to the fact that in the current version of TSQLMacro, there is no support for multiline expansion of inline macros. The macro must expand to only a single line. Through careful formatting, this problem can be averted -- in this case, the SET statement for @ExpansionText retains the formatting that would be desired if the expansion were multiline -- so editing it later will be relatively simple.
Once the COMMON_ERROR_BLOCK macro has been enabled, the UpdateData stored procedure shown above can be re-written as follows:
CREATE PROCEDURE UpdateData AS BEGIN SET NOCOUNT ON DECLARE @Error INT BEGIN TRANSACTION UPDATE Tbl ... /*#COMMON_ERROR_BLOCK()#*/ UPDATE Tbl2 ... /*#COMMON_ERROR_BLOCK()#*/ ... COMMIT TRANSACTION IF 1=0 BEGIN HandleErrors: --Report error here PRINT 'Error!' END ENDA macro could also be defined for the error handling block at the bottom of the stored procedure, to further simplify things.
In this case, if the DBA needs to add additional functionality to the error checking code, she needs to only re-define the COMMON_ERROR_BLOCK macro and re-process the routines that use it -- there is no need to edit each routine seperately.
Another example of a case in which a TSQLMacro inline macro might be used is a row-level security scheme. In such a scheme, virtually every routine in the database will generally contain a JOIN to one or more centralized security control tables, for query filtration. A simple example of such a query is:
SELECT * FROM Products JOIN SecurityTable ON SecurityTable.ProductId = Products.ProductId AND SecurityTable.UserId = @UserId
A similar query might be used for the Customers table in the same database:
SELECT * FROM Customers JOIN SecurityTable ON SecurityTable.CustomerId = Customers.CustomerId AND SecurityTable.UserId = @UserId
Every routine in the database might have a similar form to this join; it could instead be encapsulated in a macro similar to the following:
EXEC CreateMacroClass @MacroClass = 'SECURITY', @Enabled = 'Y' GO DECLARE @ExpansionText NVARCHAR(1000) SET @ExpansionText = 'JOIN SecurityTable ON SecurityTable.#COLUMN_NAME# = #TABLE_NAME#.#COLUMN_NAME# ' + 'AND SecurityTable.UserId = #USER_ID_VARIABLE#' EXEC CreateMacro @MacroClass = 'SECURITY', @MacroName = 'ROW_SECURITY_JOIN', @MacroParams = '#TABLE_NAME#, #COLUMN_NAME#, #USER_ID_VARIABLE#', @MacroExpansion = @ExpansionText, @Enabled = 'Y'
The query for the Products table within a routine could then be written as:
SELECT * FROM Products /*#ROW_SECURITY_JOIN('Products', 'ProductId', @UserId)#*/
The query for the Customers table would change similarly:
SELECT * FROM Customers /*#ROW_SECURITY_JOIN('Customers', 'CustomerId', @UserId)#*/
If the DBA needs to change the way this JOIN works slightly -- for instance, add a new predicate if a different type of security needs to be supported later -- she needs only modify the macro and re-process the routines that use it and the change will be propagated.
Macros provide a convenient way of encapsulating and maintaining common functionality across a database, in a single centralized location.
Two types of de-installation will be supported:
Please e-mail bug reports, comments, or feature suggestions to the contact address at the bottom of this page!