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 =


      SELECT tblUsers.ID,

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




      1. tblUsers.Username=?



      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

      1. e.g.

      Where 1=1




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