1 Reply Latest reply on Sep 13, 2017 6:23 AM by BKBK

    Optional Parameters..

    AdvanceSoftware_Nireland Level 1

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