Optional Parameters..

[AdvanceSoftware_Nireland]

Hi, I have a simple function that searches for a user (see below).

using cfscript. cf2016

QryGetUser =

Queryexecute("

SELECT tblUsers.ID,

1. tblUsers.OrganisaztionID,
2. tblUsers.Username,
3. tblUsers.Password,
4. tblUsers.AccessLevel

FROM

tblUsers

where

1. tblUsers.Username=?

",

[username],{datasource="myds"});

I want to have the “username” parameter in the WHERE optional.

So traditionally I have:

Wrapped the where clause in IF statements to check if a Parameter is
defined.

1. e.g.

Where 1=1

AND

If(isdefined(“parameter2”)

{

AND field2=parameter2

}

So this works just fine but gets a little cumbersome and hard to read,
however with optional parameters I figure I am going to have to build my SQL
statement dynamically in any case. I have several parts to this question:

1 – Is there a cleaner solution apart from building the SQL string

2 – Would it be bad practice to pass the whole built SQL string as a
parameter to this function, the entire select statement being built outside of
the function

3- Depending on the above answers would it then be an option to simply
create a function to run any SQL command..

[BKBK · Accepted solution]

General best-practice for designing functions suggests that you:

1) only include in the function body the parts that will remain fixed, for example, the SELECT query;

2) bring in the changing parts as arguments to the function, for example, the WHERE clauses.