CFQUERYPARAM Question - what to do when query is complex?

[Libby_H]

Running Windows, CF 2016, SQL Server db 2008R2

Creating new queries that I need assistance on how to implement CFQUERYPARAM into the code.

Below is an example of the code without the CFQUERYPARAM (my calendar dates are a text field, not a date field in the SQL Server db):

SELECT date, day FROM calendar

  WHERE (calendar.date >= '#todays_date#' and calendar.type = 'B')

Is the placement of the CFQUERYPARAM correct on the below?  I haven't been able to find anything showing this type of SQL code.

SELECT date, day FROM calendar

  WHERE <cfqueryparam value="calendar.date" cfsqltype="cf_sql_char"></cfqueryparam> =

               (<cfqueryparam value="'#todays_date#'" cfsqltype="cf_sql_char"></cfqueryparam> AND

           calendar.type = 'B')

I was able to find documented that you need not use CFQUERYPARAM around constants.

Verification would be helpful.  Thanks.

Libby H.

[Charlie Arehart]

You only need it around variables, not static values (and definitely NOT around the sql columns names). Also no need of a closing tag, so try this instead:

SELECT date, day FROM calendar

  WHERE (calendar.date >= <cfqueryparam value="'#todays_date#'" cfsqltype="cf_sql_char">

  and calendar.type = 'B')

[BKBK · Accepted solution]

Hi Libby,

also, given the comparison '>=', the cfsql type should be cf_sql_date or cf_sql_timestamp:

SELECT date, day

FROM calendar

WHERE calendar.date >=  <cfqueryparam value="'#todays_date#'" cfsqltype="cf_sql_date">

AND calendar.type = 'B'

[Libby_H]

Thanks everyone for all of the replies.

Seems I need (at least with this calendar table) to figure out what I am querying for on each table.  Unfortunately, this date field in SQL Server is  smalldatetime.  I've tried cfsqltype="cf_sql_date" and cfsqltype="cf_sql_datetime" and neither worked.

But thank you everyone for the information - they all are the correct  answers!

[Gregor_Cunningham]

Have you tried 

CF_SQL_TIMESTAMP

ColdFusion	JDBC	DB2	Oracle	MSSQL
CF_SQL_ARRAY	ARRAY
CF_SQL_BIGINT	BIGINT	Bigint		bigint
CF_SQL_BINARY	BINARY	Char for Bit Data		binaryt
CF_SQL_BIT	BIT			bit
CF_SQL_BLOB	BLOB	Blob	blob, bfile	longvarbinary
CF_SQL_CHAR	CHAR	Char	char, nchar	char
CF_SQL_CLOB	CLOB	Clob	clob,nclob
CF_SQL_DATE	DATE	Date		date
CF_SQL_DECIMAL	DECIMAL	Decimal	number	decimal
CF_SQL_DISTINCT	DISTINCT
CF_SQL_DOUBLE	DOUBLE	Double		double
CF_SQL_FLOAT	FLOAT	Float	number	real
CF_SQL_IDSTAMP	CHAR	Char	char, nchar	char
CF_SQL_INTEGER	INTEGER	Integer		integer
CF_SQL_LONGVARBINARY	LONGVARBINARY	Long Varchar for Bit Data	long raw	image
CF_SQL_LONGNVARCHAR	LONGNVARCHAR	LONGVARGRAPHIC	NVARCHAR2	ntext
CF_SQL_LONGVARCHAR	LONGVARCHAR	Long Varchar	long	text
CF_SQL_MONEY	DOUBLE	Double		double
CF_SQL_MONEY4	DOUBLE	Double		double
CF_SQL_NCHAR	NCHAR	NCHAR	NCHAR	nchar
CF_SQL_NCLOB	CLOB	NCLOB	NCLOB	nvarchar
CF_SQL_NULL	NULL
CF_SQL_NUMERIC	NUMERIC	Numeric		numeric
CF_SQL_NVARCHAR	NVARCHAR	NVARCHAR	NVARCHAR2	nvarchar
CF_SQL_OTHER	OTHER
CF_SQL_REAL	REAL	Real		real
CF_SQL_REFCURSOR	REF
CF_SQL_SMALLINT	SMALLINT	Smallint		smallint
CF_SQL_STRUCT	STRUCT
CF_SQL_SQLXML				xml
CF_SQL_TIME	TIME	Time		time
CF_SQL_TIMESTAMP	TIMESTAMP	Timestamp	date	datetime
CF_SQL_TINYINT	TINYINT			tinyint
CF_SQL_VARBINARY	VARBINARY	Rowid	raw	varbinary
CF_SQL_VARCHAR	VARCHAR	Varchar	varchar2, nvarchar2	varchar