CODEWITHSUNDEEP
sqlperldbi
frankc
frankc

Reputation: 11473

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

Assume that the actual query really needs unions and can't be efficiently solved another way. The variable in the where clause will always be the same. Is there any way I can structure this so that I only need to pass 1 argument to execute instead of passing the same argument twice?

Upvotes: 5

Views: 1137

Answers (3)

Dave Cross
Dave Cross

Reputation: 69224

You could use the list repetition operator.

$sth->execute(($value) x 2);

Upvotes: 4

Oreo
Oreo

Reputation: 553

Could try the following, I'm assuming you're 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!

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1269443

In "real" databases, you can parameterize a query and pass in 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

I am not suggesting that this is necesarily a good idea. However, I have at times found it quite useful to gather parameters into such a subquery and then join them in where needed.

Upvotes: 4

Related Questions