Discussion on: Right tool for the job, but chances are the right tool is Perl.

[Matthew D. Miller]

I still use bind variables in combination with TT, but I'm able to do "more than I could with just bind variables." For example, query.sql might contain:

select [% column_name %] from some_table where some_column = ?

and this is how I would process it with Perl (using Template::Toolkit::Simple):

my $sql = tt->render( 'query.sql', {
  column_name => $dbh->quote_identifier($column_name),
});
my $sth = $dbh->prepare($sql);
$sth->execute($some_value);

Of course, you could also do the above with sprintf, but splitting it into a separate file with TT I'm not mixing SQL and Perl. I can open my SQL up in an editor and get syntax highlighting for SQL.

I can use TT to include SQL snippets in a larger query instead of copy and pasting the same snippet over and over. In my context of higher ed, I need to limit many of the queries I run to the current term. I can throw SQL querying the term table for terms where the start date is less than sysdate and the end date is greater than sysdate into a file and then include it in other queries with where term in ([% INCLUDE active-terms.sql %]).