Interesting question on dynamic query

, ,
1

Dear All,
Hope all are doing good.
Lets say I have select query but where condition should be added if any of the option parameters( 5 parameters ) are having value in the flow service.I mean,
Let’s say there are 5 optional parameters ( a,b,c,d,e) as part of my flow service.If a,b,c,d &e are having values then my query should be
select * from <table_Name> where a=? and b=? and c=? and d=? and e=?
If I am getting only b and e then my query should be
select * from <table_Name> where b=? and e=?
If I am not getting any values as part of 5 option parameters then my query should be
select * from <table_Name>
…

Can someone has idea how to build query for this requirement

Thanks,
Anil.

2

Hi Anil,

can you try the following Pseudo-Code?


selectQuery = "select * from <table name>"
whereClause = "" (means empty string)

branch on a not empty
  true: whereClause = whereClause + "a = <value for a>"

branch on b not empty
 true: branch on whereClause.length > 0
            true: whereClause = whereClause + " and "
         whereClause = whereClause + "b = <value for b>"

branch on c not empty
 true: branch on whereClause.length > 0
            true: whereClause = whereClause + " and "
         whereClause = whereClause + "c = <value for c>"

branch on d not empty
 true: branch on whereClause.length > 0
            true: whereClause = whereClause + " and "
         whereClause = whereClause + "d = <value for d>"

branch on e not empty
 true: branch on whereClause.length > 0
            true: whereClause = whereClause + " and "
         whereClause = whereClause + "e = <value for e>"

branch on whereClause.length > 0
  true: selectQuery = selectQuery + " where " + whereClause

execute selectQuery

Missing false-branches mean “Do nothing here”.

We have used similar implementations in our project successfully.

See also the following thread (Custom SQL vs. Dynamic SQL):

Regards,
Holger

3

Thanks Holger, it meets my requirement.

Thanks,
Anil

4

Another option: stored procedure

Percio

5

Percio, can you please give more details, if possible give some pseudo code.

Thanks,
Anil.

6

The logic is basically the same. I’m just suggesting that you should perhaps host it on the database, which would make more sense to me. In that case, from the Integration Server, you would always call the stored procedure in exactly the same way every time, by mapping your variables to the procedure’s input, regardless of whether the variables were null or not. The stored procedure would be the one responsible for analyzing each variable to determine whether it should be included in the where clause or not.

Percio