TSQLMacro
macro expansion for sql server

Introducing TSQLMacro

Problem: You're developing a SQL Server-based system using stored procedures. Some of the stored procedures don't work quite right. So you embed PRINT and SELECT statements in them. But it's up to you to get them out before the stored procedure rolls to production. Will you comment them out? Delete them? What if you need to debug again? You'll have to re-insert them or uncomment them. And what if you miss one when rolling to production?

Problem: You're developing a SQL Server-based system with stored procedures that must change depending on environment. In some environments some code fragments aren't appropriate. They need to be turned off. And other code fragments need to be turned on. Will you do this by hand? Do you trust that it will be done properly? Or will you write multiple versions of every stored procedure in your system?

Problem: You're developing a SQL Server-based system with many stored procedures, each of which has a very similar series of JOINs used for security -- each differentiated only by a single table or column name. The security scheme changes a bit. You need to make a simple modification to the JOINs -- all 2,000 of them.

Solution

Each of these problems are issues I've seen first-hand in SQL Server shops I've worked in. And none of them were easy to solve. In an attempt to avoid these issues going forward, I've created the TSQLMacro framework.

TSQLMacro behaves similarly to macros in C/C++. A developer can define an inline macro or a macro directive (in TSQLMacro lingo, called a "macro class") and embed it in a stored procedure, UDF, view, or trigger (collectively referred to as "routines"). Once the routine has been applied on the server, the TSQLMacro preprocessor can be run, which will expand -- or collapse -- the defined macros within the routine, based on whether or not those macros are enabled for the database.

For example, take the first problem again. Debug code.

Assume that a macro class called "DEBUG" has been defined and enabled in the current database. The following stored procedure can be written:

CREATE PROCEDURE GetSomeData
	@Param1 INT,
	@Param2 VARCHAR(20)
AS
BEGIN
	SET NOCOUNT ON

	SELECT ...
	...
	INTO #Temp
	...


	--Sometimes #Temp doesn't seem to fill properly... Why?

	/*#IFDEF(DEBUG)
		SELECT @Param1, @Param2

		SELECT * FROM #Temp		
	#ENDIF#*/


	UPDATE ...
	SET ...
	...
END

The important section to notice is the part starting with /*#IFDEF and ending with #ENDIF#*/. This section is commented out -- but #IFDEF and #ENDIF# are directives understood by the TSQLMacro preprocessor -- and DEBUG is the name of the Macro Class. This code tells the preprocessor, "if Macro Class DEBUG is created and enabled, uncomment this code. Otherwise, leave it commented."

When this code starts having problems and the developer needs to debug, turning back on the code is as simple as running the TSQLMacro preprocessor:

EXEC MacroProcess 
	@RoutineName = 'GetSomeData'

Assuming that DEBUG was enabled, the routine would now look something like this (the preprocessor automatically ALTERs the routine -- the developer does not need to touch it in any way):

CREATE PROCEDURE GetSomeData
	@Param1 INT,
	@Param2 VARCHAR(20)
AS
BEGIN
	SET NOCOUNT ON

	SELECT ...
	...
	INTO #Temp
	...


	--Sometimes #Temp doesn't seem to fill properly... Why?

	/*#IFDEF(DEBUG) E(A36B501E-5C21-4EB8-B2C9-06E54B5395DC)#*/
		SELECT @Param1, @Param2

		SELECT * FROM #Temp		
	/*E(A36B501E-5C21-4EB8-B2C9-06E54B5395DC) #ENDIF#*/


	UPDATE ...
	SET ...
	...
END

The comment blocks are now disabled -- and the code between them is enabled. A GUID has been inserted for verification later, when the comment block is collapsed. Collapsing the comment block will be as simple as disabling the DEBUG class and re-running the preprocessor.

The other type of directive supported by TSQLMacro are "inline" macros. These are parameterized code blocks that "expand" into larger code blocks. Consider the third problem, the security JOINs. How would we handle the situation using TSQLMacro "inline" macros?

We could define a macro called "SECURITY_JOIN" which would take two parameters: "#TABLE_NAME#", and "#COLUMN_NAME#". Note that TSQLMacro parameters are always delimited by #.

This macro will be defined with the following "MacroExpansion" code (the macro expansion is the template that will be used when expanding the macro):

"JOIN SecurityTable ON #TABLE_NAME#.#COLUMN_NAME# = SecurityTable.SecurityId"

Every input for #TABLE_NAME# will be replaced with the input table name. And every input for #COLUMN_NAME# will be replaced with the input column name.

So the following two stored procedures might be created:

CREATE PROCEDURE GetRecentSalesData
AS
BEGIN
	SET NOCOUNT ON

	SELECT Sales.*
	FROM Sales
	/*#SECURITY_JOIN('Sales', 'SalesId')#*/
	WHERE Sales.SalesDate >= GETDATE() - 7
END

CREATE PROCEDURE GetAllProductData
AS
BEGIN
	SET NOCOUNT ON

	SELECT Products.*
	FROM Products
	/*#SECURITY_JOIN('Products', 'ProductsId')#*/
END

When the developer rolls these stored procedures onto a SQL Server, the SECURITY_JOIN macro will be defined and enabled. Then the preprocessor can be run, which will expand them to something like:

CREATE PROCEDURE GetRecentSalesData
AS
BEGIN
	SET NOCOUNT ON

	SELECT Sales.*
	FROM Sales
	/*#SECURITY_JOIN('Sales', 'SalesId') X(66033C67-C2F0-4325-8094-F7F4A44F656B)#*/JOIN ->
-> SecurityTable ON Sales.SalesId = SecurityTable.SecurityId/*#Y(66033C67-C2F0-4325-8094-F7F4A44F656B)#*/
	WHERE Sales.SalesDate >= GETDATE() - 7
END
-> represents a line continuation.

CREATE PROCEDURE GetAllProductData
AS
BEGIN
	SET NOCOUNT ON

	SELECT Products.*
	FROM Products
	/*#SECURITY_JOIN('Products', 'ProductsId') X(B0D3383C-BB3B-46EF-8BA4-BDF50279DE2C)#*/JOIN ->
-> SecurityTable ON Products.ProductsId = -> SecurityTable.SecurityId/*#Y(B0D3383C-BB3B-46EF-8BA4-BDF50279DE2C)#*/
END
-> represents a line continuation.

Now if the security scheme needs to change a bit, e.g. a new type of security is added for another part of the system and an additional predicate is added, "SecurityTable.SecurityType = 1", all the developer needs to do is change the macro definition and re-run the preprocessor. Every stored procedure will automatically be updated to the new definition.

So if the macro expansion definition is updated to:

"JOIN SecurityTable ON #TABLE_NAME#.#COLUMN_NAME# = SecurityTable.SecurityId AND SecurityTable.SecurityType = 1"

When the preprocessor is run, the GetRecentSalesData procedure will now look like:

CREATE PROCEDURE GetRecentSalesData
AS
BEGIN
	SET NOCOUNT ON

	SELECT Sales.*
	FROM Sales
	/*#SECURITY_JOIN('Sales', 'SalesId') X(B115562F-B651-43D5-87A5-117E068A213F)#*/JOIN ->
-> SecurityTable ON Sales.SalesId = SecurityTable.SecurityId AND SecurityTable.SecurityType = ->
-> 1/*#Y(B115562F-B651-43D5-87A5-117E068A213F)#*/
	WHERE Sales.SalesDate >= GETDATE() - 7
END
-> represents a line continuation.

Note the modified GUID -- every time the preprocessor is run, each directive in the stored procedure is collapsed, then only those that are enabled are re-expanded. This is done to maintain integrity and consistency throughout the routines.

If a time ever comes that security should be disabled, the developer needs only disable the SECURITY_JOIN macro and re-run the preprocessor. The SecurityTable JOIN will now be gone.

Conclusion

TSQLMacro provides a convenient framework for packaging debug code and keeping common TSQL code fragments in a central location in the database. Its goal is to make code maintenence easier and more efficient, and provide a mechanism for building better, more robust routines.
adam machanic
contact: amachanic@datamanipulation.net