Highlighted

Optional Parameters..

Community Beginner ,
Sep 12, 2017

Copy link to clipboard

Copied

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..

Adobe Community Professional
Correct answer by BKBK | Adobe Community Professional

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.

Views

193

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Optional Parameters..

Community Beginner ,
Sep 12, 2017

Copy link to clipboard

Copied

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..

Adobe Community Professional
Correct answer by BKBK | Adobe Community Professional

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.

Views

194

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Sep 12, 2017 0
Adobe Community Professional ,
Sep 13, 2017

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 13, 2017 1