You occasionally have to write different SQL depending on the situation. I use the following code to basically build up the SQL query, and keep track of the values I'm going to need to bind. (it's a little more abstracted than you probably need -- I have multiple search engines that use this module):

sub _AddSQL {
        my ($self, $sql, @bindvars) = @_;
        
        push @{$self->{'where'}}, $sql;
        push @{$self->{'bind'}}, @bindvars;
}
...
sub _SendSearch {
        my $self = shift;

        my $dp = $self->dataprovider();

        my $db = $dp->GetDatabase() or
                return $self->_ThrowError('500 Database unavailable');

        my $where = 'AND  ('. join ( ') and (', @{$self->{'where'}}). 
+') ';

...
        my $sql = $dp->sql_prefix() . $where . $dp->sql_suffix();
        
        $sth = $db->prepare( $sql )
                or return $self->_ThrowError ( '500 Cannot prepare sea
+rch', debug => $db->errstr );
        $sth->execute( @{$self->{'bind'}} )
                or return $self->_ThrowError ( '500 Cannot execute sea
+rch', debug => $db->errstr );

        my %fields;
        $sth->bind_columns( \(@fields{ @{ $sth->{'NAME_lc'} } } ) );
        
        my @records = ();
        
        while ( $sth->fetch() ) {
                push @records, $self->_ProcessRecord( \%fields );
        }
        
        return $self->dataprovider()->_PackageResults ( $numFound, \@r
+ecords, debug => $where );
}
[download]

The code that inherits it defines a series of handlers for different search terms, eg:

(and crap -- would you believe, by posting this, I noticed a logic error in the code ... if 'wavetype' was defined, I wasn't processing the range match)