my @where;
my $sql = 'select * from table';

push @where, 'column1 = "' . $var1 . '" ' if defined $var1;
push @where, 'column2 = "' . $var2 . '" ' if defined $var2;
push @where, 'column3 = "' . $var3 . '" ' if defined $var3;

$sql = $sql . ' where ' . join(' and ', @where);
[download]

When you put the values into an array, it would be even better:

my @values = (undef, $var1, $var2, $var3);
my @where;
my $sql = 'select * from table';

foreach my idx (1..3) {
  push @where, qq(column$dx = "$values[$idx]") if defined $values[$idx
+];
}

$sql = $sql . ' where ' . join(' and ', @where);
[download]

... and don't forget to handle the case where no values have been supplied (return an error or all results). e.g.

$sql = $sql . ' where ' . join(' and ', @where) if defined $where[0];
[download]

map{$a=1-$_/10;map{$d=$a;$e=$b=$_/20-2;map{($d,$e)=(2*$d*$e+$a,$e**2
-$d**2+$b);$c=$d**2+$e**2>4?$d=8:_}1..50;print$c}0..59;print$/}0..20
[download]

Tom Melly, pm (at) cursingmaggot (stop) co (stop) uk

Except, of course, that interpolating user-supplied input directly into your SQL statements is bad ju-ju. Much better would be:

my @where;
my @values;
my $sql = 'select * from table';

if defined $var1 { push @where, 'column1 = ?'; push @values, $var1; }
if defined $var2 { push @where, 'column2 = ?'; push @values, $var2; }
if defined $var3 { push @where, 'column3 = ?'; push @values, $var3; }

$sql = $sql . ' where ' . join(' and ', @where);

my $sth = $dbh->prepare_cached($sql);
$sth->execute(@values);
[download]

or

my @values = (undef, $var1, $var2, $var3);
my @where;
my $sql = 'select * from table';

my @defined_values;
foreach my idx (1..3) {
  if defined $values[$idx] {
    push @where, qq(column$dx = ?);
    push @defined_values, $values[$idx];
  }
}

$sql = $sql . ' where ' . join(' and ', @where);

my $sth = $dbh->prepare_cached($sql);
$sth->execute(@defined_values);
[download]

my @clauses;
push @clauses, 'column1' if defined $var1;
push @clauses, 'column2' if defined $var2;
push @clauses, 'column3' if defined $var3;
my $query = "select * from table";
if (@clauses) {
    $query .= " where " . join ' and ', map {"$_ = ?"} @clauses
}
my $result = $dbi->selectall_arrayref($query, {}, grep {defined} $var1
+, $var2, $var3);
[download]