8
When riding SELECTS
at runtime we always have the problem of knowing what may or may not be coming depending on the choices made by the user.
When mounting the clause WHERE
we come across the question of whether or not to add the AND
before each condition.
If it’s the first condition then you won’t have the AND
, however if it is the second or greater than this we add to the beginning of the condition AND
.
In order not to be hostage to this condition, having to analyze it at all times I add an innocuous clause and then I can add AND
to all the others.
Thus my clause WHERE
initial is WHERE 0 = 0
, then I can add the AND
to all other conditions.
At the time of execution my clause WHERE
will be like this: WHERE 0 = 0 AND condicao1 = 'cond1' AND condicao2 = 'cond2' AND condicao3 = 3
or just like that: WHERE 0 = 0
and will run smoothly.
When we ride a UPDATE
at runtime we have the same problem, but the question now is the comma or the total lack of parameters.
We start with "UPDATE tabelax SET"
and we add columns as the program finds these markups.
In the first we add "campo1 = 'campo1'" + ","
, in the second "campo2 = 'campo2' + ","
. Note that we could not add the comma if the field2 was the last field filled because otherwise our final command would be wrong. UPDATE tabelax SET campo1 = 'campo1', campo2 = 'campo2', WHERE condicao
this wrong. In other words, you have to keep asking if it is the last one or not, or add it at all and when you finish the loop check if the last character is a comma and remove it before adding the clause WHERE
.
Finally, the question: there is no way, as the case of WHERE
,
make it easier, more elegant or be smart to solve it?
In what language is riding the query?
– Maniero
SQL-Server or Oracle
– Reginaldo Rigo
Do not use CAPSLOCK unnecessarily.
– Guilherme Nascimento
You ride the query inside stored Procedure?
– Maniero
No. The query is executed in the client itself, via Tsimpledataset.
– Reginaldo Rigo
Then it would be Delphi?
– Maniero
Could be. But not just Delphi. I write this in Delphi, C# and Java as well.
– Reginaldo Rigo
I think this question is still a little broad. There is a lack of an effective example of the problem. As it stands, Ricardo’s response covers most cases.
– EMBarbosa
@Reginaldorigo Did any of the answers solve your problem? Do you think you can accept one of them? You would help the community by identifying the best solution for you
– Maniero