cfqueryparam

Verifies the data type of a query parameter and, for DBMSs that
support bind variables, enables CFML to use bind variables
in the SQL statement. Bind variable usage enhances performance
when executing a cfquery statement multiple times.

This tag is nested within a cfquery tag, embedded in a query SQL
statement. If you specify optional parameters, this tag performs
data validation.

 <cfqueryparam>

 cfqueryparam();

Attribute Reference for the cfqueryparam tag

value

Required: No
Value that CFML passes to the right of the comparison
operator in a where clause.

If CFSQLType is a date or time option, ensure that the date
value uses your DBMS-specific date format. Use the
CreateODBCDateTime or DateFormat and TimeFormat functions
to format the date value.

cfsqltype

Required: No
SQL type that parameter (any type) is bound to. As of CF 11+ or Lucee 4.5+ you can omit the cf_sql_ prefix. Values:
  • cf_sql_bigint
  • cf_sql_bit
  • cf_sql_char
  • cf_sql_blob
  • cf_sql_clob
  • cf_sql_date
  • cf_sql_decimal
  • cf_sql_double
  • cf_sql_float
  • cf_sql_idstamp
  • cf_sql_integer
  • cf_sql_longvarchar
  • cf_sql_money
  • cf_sql_money4
  • cf_sql_numeric
  • cf_sql_real
  • cf_sql_refcursor
  • cf_sql_smallint
  • cf_sql_time
  • cf_sql_timestamp
  • cf_sql_tinyint
  • cf_sql_varchar
  • integer
  • varchar
  • timestamp
  • date

maxlength

Required: No
Maximum length of parameter.

scale

Required: No
Number of decimal places in parameter. Applies to CF_SQL_NUMERIC and CF_SQL_DECIMAL.

null

Required: No
Whether parameter is passed as a NULL value.

true: ignores the value attribute and passes NULL
false: passes the value attribute

list

Required: No
Yes: The value attribute value is a delimited list
No: it is not

separator

Required: No
Character that separates values in list, in value attribute. Values:
  • ,
  • ;
  • |
  • :

Examples sample code using the cfqueryparam tag


Basic example

Shows how to use a cfqueryparam tag within cfquery.

<cfquery name="news">
    SELECT id,title,story
    FROM news
    WHERE id = <cfqueryparam value="#url.id#" cfsqltype="cf_sql_integer">
</cfquery>

Using a list on an IN statement

Assumes url.idList is a comma seperated list of integers, eg: 1,2,3

<cfquery name="news">
    SELECT id,title,story
    FROM news
    WHERE id IN (<cfqueryparam value="#url.idList#" cfsqltype="cf_sql_integer" list="true">)
</cfquery>

cfscript exuivalent of cfqueryparam

CF 11+ script syntax using queryExecute and full array notation

exampleData = queryNew("id,title","integer,varchar",[{"id":1,"title":"Dewey defeats Truman"},{"id":2,"title":"Man walks on Moon"}]);

result = queryExecute(
  "SELECT title FROM exampleData WHERE id = ?", 
  [
    { value=2, cfsqltype="cf_sql_varchar" }
  ],
  { dbtype="query" }
);

writeOutput( result.title[1] );

Expected Result: Man walks on Moon


cfscript equivalent of cfqueryparam

CF 11+ script syntax using queryExecute and array shorthand

exampleData = queryNew("id,title","integer,varchar",[{"id":1,"title":"Dewey defeats Truman"},{"id":2,"title":"Man walks on Moon"}]);

result = queryExecute(
  "SELECT title FROM exampleData WHERE id = ?", 
  [ 2 ],
  { dbtype="query" }
);

writeOutput( result.title[1] );

Expected Result: Man walks on Moon


cfscript exuivalent of cfqueryparam

CF 11+ script syntax using queryExecute and struct notation

exampleData = queryNew("id,title","integer,varchar",[{"id":1,"title":"Dewey defeats Truman"},{"id":2,"title":"Man walks on Moon"}]);

result = queryExecute(
  "SELECT title FROM exampleData WHERE id = :id", 
  { id = 2 },
  { dbtype="query" }
);

writeOutput( result.title[1] );

Expected Result: Man walks on Moon


Fork me on GitHub