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.



Attribute Reference for the cfqueryparam tag

Name Required Default Description
value 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 No SQL type that parameter (any type) is bound to.

Example 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
maxlength No Maximum length of parameter.
scale No Number of decimal places in parameter. Applies to
null No Whether parameter is passed as a null value.

Yes: tag ignores the value attribute
No: does not

Example Values:

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

Example Values:

  • YES
  • NO
separator No Character that separates values in list, in value attribute.

Example Values:

  • ,
  • ;
  • |
  • :

Examples sample code using the cfqueryparam tag

Example CFQuery with CFQueryParam

Shows how to use a cfqueryparam tag within cfquery.

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

Example List with CFQueryParam

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">)

Example CFQueryParam in cfscript and CF11

CF 11 script syntax in a function with cfqueryparam

public any function myFunction(string myParam="")
        var res = "";
            res = queryExecute(
                "SELECT Column
                 FROM  myDatabase
                  WHERE Column = :param"
                param={value=arguments.myParam, cfsqltype="cf_sql_varchar"}
                return qry.execute().getPrefix();

Fork me on GitHub