Geek asks and answers

Repeated Arguments in a Prepared Statement

consider a query that looks something like this:


my $query=<<QUERY;

select * from foo1 where col < ?
union all
select * from foo2 where col < ?
QUERY

Suppose that the actual request really needs alliances and cannot be effectively resolved in any other way. The variable in the where clause will always be the same. Is there a way to structure this so that I only need to pass one argument to execute instead of passing the same argument twice?

+5
source share
3 answers

You can use the list repeat operator.

$sth->execute(($value) x 2);
+4
source

In "real" databases, you can parameterize the query and pass the query as an argument. Here is an alternative solution:

with const as (select ? as val)
select *
from ((select foo1.*
       from foo1 cross join const
       where col < const.val
      ) union all
      (select foo2.*
      from foo2 cross join const
      where col < const.val
     )) t

, . , , , .

+4

You could try the following: I assume you are passing an integer to the where clause ...

DECLARE @variableName as int

SET @variableName = ? --the value gets passed here once

select * from foo1 where col < @variableName -- and gets used here
union all
select * from foo2 where col < @variableName -- and here!
+3
source

More articles:


All Articles