Skip to main content
Ben Nadel at CFUNITED 2010 (Landsdown, VA) with: Lola Lee Beno
Ben Nadel at CFUNITED 2010 (Landsdown, VA) with: Lola Lee Beno@lolajl )

Cannot Use ColdFusion CFQueryParam With SQL TOP Directive

By on
Tags: ,

I just came across a strange little error that took me a few minutes to debug. I was running this SQL query:

DELETE FROM
	reward
WHERE
	order_id = 0
AND
	delivery_id = <cfqueryparam value="#id#" cfsqltype="CF_SQL_INTEGER" />
AND
	id IN
	(
		SELECT TOP <cfqueryparam value="#(qUnused.reward_count - FORM.quantity)#" cfsqltype="CF_SQL_INTEGER" />
			id
		FROM
			reward
		WHERE
			order_id = 0
		AND
			delivery_id = <cfqueryparam value="#id#" cfsqltype="CF_SQL_INTEGER" />
		ORDER BY
			id DESC
	)

... and it was throwing this error:

[Macromedia][SQLServer JDBC Driver][SQLServer]
Line 11: Incorrect syntax near '@P2'.

Looking at the debugging SQL:

DELETE FROM
	reward
WHERE
	order_id = 0
AND
	delivery_id = (param 1)
AND
	id IN
	(
		SELECT TOP (param 2)
			id
		FROM
			reward
		WHERE
			order_id = 0
		AND
			delivery_id = (param 3)
		ORDER BY
			id DESC
	)

... I can see that Param 2 is the SQL TOP value. Going in and take the CFQueryParam out and replace it with:

SELECT TOP #(qUnused.reward_count - FORM.quantity)#

... things work just fine. Apparently, you cannot use the CFQueryParam with the SQL TOP statement. Not sure what this is all about. It seems like this is a place where SQL injection could occur (especially in my case where the TOP value is being derived from a FORM value). Perhaps this is just not the best way for me to accomplish this goal.

Want to use code from this post? Check out the license.

Reader Comments

15 Comments

There was a bug in recent updates to CF that caused using Maxrows on a query to be problematic. I went changing some old queries that used the maxrows to use top instead and came across this as well. I don't think this is a CF but rather a MS SQL issue.

15 Comments

There was a bug in recent updates to CF that caused using Maxrows on a query to be problematic. I went changing some old queries that used the maxrows to use top instead and came across this as well. I don't think this is a CF but rather a MS SQL issue.

15 Comments

Sorry my comment got posted twice, but FYI I got a 500 null error when I submitted it both times and didn't think it went through...also I checked remember my information and it does not seem to have done so. Just letting you know.

15,155 Comments

Thanks for letting me know. Something has gone a bit cookey with the comment posting in the last few days. I have to figure out what happened. I am on it!

48 Comments

How about this Ben? Before the query do:

declare @param int
set @param = <cfqueryparam value="#id#" cfsqltype="CF_SQL_INTEGER" />

then in your query do a:

select top @param......

not tested, but should work....

15,155 Comments

Todd,

Nice thought. I guess it just can't handle binding in general? As Brian says above, at least it's not a ColdFusion error, but one of the SQL server.

48 Comments

If it's that important, you could always make the sql dynamic like this:

DECLARE @param int
set @param = <cfqueryparam value="#id#" cfsqltype="CF_SQL_INTEGER" />

DECLARE @sql nvarchar(4000)
SELECT @sql = ' SELECT top ' + @param + 'columns and stuff'+
' FROM ...' +
' WHERE...'

EXEC sp_executesql @sql

15 Comments

In general, unless it is completely necessary, I avoid dynamic sql like that. The reason is that it is my understanding that when using dynamic sql like that MS SQL recompiles the execution plan on every request - so it would negate the performance benefit of using the queryparam to create a prepared statement I believe. This would also be the case even if your dynamic SQL was in a stored procedure...execution plan is recreated on each call...

15,155 Comments

Todd,

Sorry, that goes way beyond my understanding. I mean, I get what you are doing, but I know zero about dynamic SQL execution :)

48 Comments

What about this Brian/Ben?

<cfquery...>
set rowcount <cfqueryparam value="1" cfsqltype="cf_sql_integer">
select *
from tbl...
where stuff...
set rowcount 0
</cfquery>

Would this cache the execution plan? It solves the TOP n issue, and it works...

194 Comments

I would imagine the reason it doesn't work is you can't bind a variable to the TOP directive.

Why not just use:

SELECT TOP #val(qUnused.reward_count - val(trim(FORM.quantity)))#

This should prevent SQL Injections and the val() function will make sure your value is seen as an integer.

15,155 Comments

@Todd,

No worries on being a wh*re ;) Comments rock. Thinking out-loud rocks.

@Dan,

Nice tip on Val().

Good stuff all around.

1 Comments

Hi Ben, came across this when I ran into the issue in CF9, you can also fix it by multiplying by 1.

SELECT TOP (1*<cfqueryparam value="#arguments.pagesize#" cfsqltype="cf_sql_integer">)

Works just fine where this fails

SELECT TOP <cfqueryparam value="#arguments.pagesize#" cfsqltype="cf_sql_integer">