>Where the use of : is optional, as best practice should : be used or avoided.
>Or in such cases use of : is irrelevant in terms of performance.

Hi!

I think your question can be interpreted a couple of ways. If the question is "should I use a parameter or a constant in a query", then there's no big difference. However, using a parameter has two potentially important advantages: If a query will be executed several times, then it is much quicker to prepare a query once and just change the parameter(s) for each execution than to execute several different, but similar queries. Moreover, a query with parameters are not prone to sql injection attacks the same way as a dynamically created query.

If the question is "should I use parameters or separate queries if a field may or may not be part of the WHERE clause", then there's no set answer, using it like

SELECT ...
FROM ...
WHERE FieldName = COALESCE(:Parameter, FieldName)

means that no index can be used for FieldName, which is only OK on small tables or if there are other parts of the WHERE clause that can utilise indexes to limit the result set.

Please rephrase Your question if it is different from both ways I've interpreted it or something is still unclear.

HTH,
Set