Macros can be used to reproduce code which appears frequently, with very few modifications, in any number
of stored procedures. For example, a macro for code which either rolls back or commits a transaction after a change to
a database table can be written as follows: (assume _retcode has
been defined earlier)
define(_rollback_or_commit,`
changecom(!!,!!) /* `$0($*)' */ changecom(/*,*/)
if ( @@error != 0 ) begin
rollback transaction
return _retcode($1)
end
else begin
commit transaction
return _retcode($1)
end ')
Within SQL code this macro is used as follows:
begin tran
insert MyTable values (1,2,3)
_rollback_or_commit(_INSERT)
The line
changecom(!!,!!) /* `$0($*)' */ changecom(/*,*/)
displays the precise macro used to produce the SQL code of its expansion. Adding this line to macros
makes them self-documenting. Please note that the standard
comment symbol for m4 is #, a poor choice for tempdb users. The built-in macro changecom() lets you redefine the comment symbol for m4. In
the include file macro_def.m one of the first things to do is
redefine comment symbols to be consistent with SQL. Assuming this has been done, what is actually
happening in the above macro is a temporary redefinition of comment symbols to
be !! (just until the end of the line). Then the expression /* `$0($*)' */ is expanded
leaving the /* and */ in place. Finally,
comment symbols are changed back to be consistent with SQL. The symbol
$0 stands for the name of the current macro. The symbol $* stands for "all arguments of the current macro separated by commas". The quotes,
` and ', guarantee that the current macro is printed without being
evaluated. Hence this macro prints whatever the macro being expanded looks like inside SQL
comment delimiters.
pletch@mcs.newpaltz.edu
