I've written a bit of dynamic SQL many different ways... I generally group the statements by the FROM clause. In this case, the format of your queries look pretty similar. I might try Something Like(tm) this:
my ($sr_from, $sr_where);
if( $ref_sid ) {
$sr_from = ", Search_Results SR ";
$sr_where = "AND P.id = SR.page_id AND SR.search_id = ? ";
push @query_args, $ref_sid;
}
my $mquery =<<"SQL"
INSERT INTO Search_Results ( search_id, page_id )
SELECT ?, P.id
FROM
Pages P,
Documents D,
Dirs DI
$sr_from
WHERE MATCH ( P.pgdata ) AGAINST ( ? IN BOOLEAN MODE )
AND P.doc_id = D.id
AND D.dir_id = DI.id
$sr_where
SQL
I'm unsure if the bind parameter '?' is buying you anything in this query as each statement prepare() would have to see what is being SELECTed. Either way, be careful not let the interpolated variables be changed through the program interface.
All said, I'm thinking that SQL::Builder does look very interesting.