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();

cfqueryparam Attribute Reference

value String

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 String

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_longnvarchar
  • 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
  • cf_sql_nvarchar
  • integer
  • varchar
  • timestamp
  • date

maxlength Numeric

Maximum length of parameter.

scale Numeric

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

null boolean

Whether parameter is passed as a NULL value.

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

list boolean

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

separator char

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