MidLifeXis was so kind as to recommend my module DBIx::PreQL. It's aim is to simplify the horrendous task of maintaining dynamically generated SQL.

Here's one way to handle the your particular case.

my $preql = <<'PREQL'
 *      SELECT
 *          Name
 *      FROM
 CUST       Customer
 SALE       Sales
 *      WHERE
 CUST      CustID = ?customer_id?
 SALES     SalesID = ?sales_id?     
PREQL

my ($query, @params) = DBIx::PreQL->build_query(
     query => $preql,
     data => {
        sales_id => $salesid,
        customer_id => $custid,
     },
    wanted => $x > 10 ? 'CUST' : 'SALES'
 );

my $got = $db->selectall_hashref( $query, @params );
[download]

Why do all this stuff?

• You get the placeholders you wanted for your dynamic query.
• This is the least sucky way I've found to build dynamic SQL. The code I maintain has tons of dynamic SQL and I spent years experimenting before I hit upon this approach.
• You will get a good error message if you put yourself in a condition where you are expecting a customer id but one isn't available.

In fact, at $WORK, we have found it worthwhile to convert static queries to PreQL. It has helped with debugging and maintenance. Because all the placeholders are labeled, they are easier to understand. PreQL also provides useful error messages that identify missing, but expected placeholder values.

You should use placeholders.

$query1="(select Name from Customers where CustId=?)";
$query2="(select Name from Sales where SalesId=?)";

$sth1=$dbh->prepare($query1);
$sth2=$dbh->prepare($query2);

if ($x>10) {
 $sth1->execute($custid);
}
else {
 $sth2->execute($salesid);
}
[download]

If you have a complex query you can write this way for the easier readability:

$query1 = 'select Name from Customers where CustId = :custid';
$query2 = 'select Name from Sales where SalesId = :salesid';

$sth1 = $dbh->prepare($query1);
$sth2 = $dbh->prepare($query2);

if ($x > 10) {
  $sth1->bind_param('custid', $custid);
  $sth1->execute();
  while ($row = $sth1->fetchrow_hashref) {
    ...
  }
} else {
  $sth2->bind_param('salesid', $salesid);
  $sth2->execute();
  while ($row = $sth2->fetchrow_hashref) {
    ...
  }
}
[download]

'prepare' can be replaced with 'prepare_cached' in order to avoid multiple preparation. Excerpt from DBI man page:

Like "prepare" except that the statement handle returned will be store
+d in a hash associated with the $dbh. If another call is made to "pre
+pare_cached" with the same $statement and %attr parameter values, the
+n the corresponding cached $sth will be returned without contacting t
+he database server.
[download]

Update: I am not sure that this kind of named binding work with MySQL.

because the query parameters varry too :

if ($x>10) { $query="(select Name from Customers where CustId='$custid
+')" } else { $query="(select Name from Sales where SalesId='$salesid'
+ and CustId='$custid')" }
[download]

so when preparing and executing the query with the first query:

$sth->execute(?)
[download]

with the second:

$sth->execute(?,?)
[download]

so I can't have a single execute covering both occasions

Did you notice that my version has two separate statement handles that are being executed? You can do the same thing with yours. Prepared statements with placeholders will likely be faster than constructing queries on the fly and executing them, and certainly much safer.

How many possible queries can you need to construct that you can't afford to use prepared statements and placeholders?

my ( $query, @args );
if ( $x > 10 ) {
    $query = 'select Name from Customers where CustID = ?';
    @args = ( $custid );
} else {
    $query = 'select Name from Sales where SalesId = ? and CustID = ?'
+;
    @args = ( $salesid, $custid );
}

my $sth = $dbh->prepare( $query );
$sth->execute( @args );
[download]

Your code seems very badly organized if you can really mix into a single line of execution like that. You might look at refactoring the design. That said, caching the cached statements yourself is trivial with Perl–

my %query_map; # Actually declared in higher (more outer) scope.
if ( $x > 10 ) {
    $query = "SELECT Name FROM Customers WHERE CustId = ?";
}
else {
    $query = "SELECT Name FROM Sales WHERE SalesId = ?";
}
$query_map{$query} ||= $dbh->prepare($query);

$query_map{$query}->execute(@args_that_will_match_query);
[download]

See also DBIx::PreQL for a tool to help manage query clauses like this.

if ( $x > 10 ) {
    $query = '(select Name from Customers where CustId=?)'
    $cust_sth = $dbh->prepare( $query );
    $cust_sth->execute( $custid );
} else {
    $query = '(select Name from Sales where SalesId=? and SalesTerrito
+ry=?)'
    $sales_sth = $dbh->prepare( $query );
    $sales_sth->execute( $salesid, $territory );
}
[download]

since I can't use placeholders ... since the query won't be cached

Why not? The example you showed would allow both these things.

I am worried about ... performance

Instead of worrying, implement and measure. Or at least be more specific, e.g. how many of these queries per hour?

because the query parameters varry too :

if ($x>10) {
$query="(select Name from Customers where CustId='$custid')"
}
else {
 $query="(select Name from Sales where SalesId='$salesid'
and CustId='$custid')"
}
[download]

so when preparing and executing the query with the first query:

$sth->execute(?)
[download]

with the second:

$sth->execute(?,?)
[download]

so I can't have a single execute covering both occasions

Actually, it's not too difficult to use the same execute statement:

$SQL = "select name from customers where 1=1";

if ($fl_have_cust_id) {
   $SQL .= " and custid=?";
   push @args, $cust_id;
}

if ($fl_have_sales_id) {
   $SQL .= " and salesid=?";
   push @args, $sales_id;
}

$ST = $DB->prepare($SQL);
$ST->execute(@args);
[download]

We have a hardcoded 1=1 condition so we can just add " and <condition>" to $SQL for each new condition we want.

...roboticus

When your only tool is a hammer, all problems look like your thumb.

Construct placeholders queries with hard coded options on the fly, no injection possible then.

edit

If you cache them after first use in a hash, you won't have a performance problem.

Eg create a custom function getting a hash ( field =>entry,... ) and preparing only if new and returning result.

Cheers Rolf
_{(addicted to the Perl Programming Language and ☆☆☆☆ :)
Je suis Charlie!}

What DBMS are you building this on?

Mysql,does it make a difference?

Different DBMSen have different capabilities, yes. (Not to mention that people have varying interest in different systems)