Duncan's blog

August 5, 2008

cfqueryparam and stored procedures

Filed under: Coldfusion — duncan @ 12:17 am
Tags: , , , ,

Came across this little anomaly. If you’re using <cfqueryparam> and calling a stored procedure, you need to prefix the procedure call with EXEC. That might seem obvious, but I couldn’t find any mention of it elsewhere.

Let me explain further. Say you have the following code:

<cfquery name="doStuff" datasource="#foobar.dsn#">
EXEC sp_doStuff
@foo = '#Form.Foo#'
</cfquery>

According to this article, the EXEC statement can actually be removed. It states ‘you can execute a stored procedure without using the EXECUTE keyword if the stored procedure is the first statement in the batch‘. (my emphasis) So our query could also be written as:

<cfquery name="doStuff" datasource="#foobar.dsn#">
sp_doStuff
@foo = '#Form.Foo#'
</cfquery>

However, if you then try and use <cfqueryparam> here, the query will break. I’d used Daryl Banttari’s ‘parameterize Queries’ tool to automatically update all our queries with cfqueryparams. It had found a procedure call like the second example above, and changed it to something like:

<cfquery name="doStuff" datasource="#foobar.dsn#">
sp_doStuff
@foo = <cfqueryparam value="#Form.Foo#">
</cfquery>

But this was throwing a syntax error. I couldn’t spot the problem, everything looked fine in terms of the parameters being passed to the procedure. I even added the CFSQLType attribute, but that made no difference. Eventually I spotted the only difference between this query and others that called stored procedures and were working, was the missing EXEC statement.

I think I read somewhere that what CFQueryParam is actually doing in the background is something like:
<cfquery name="doStuff" datasource="#foobar.dsn#">
DECLARE @Param1 varchar;
EXEC sp_doStuff
@foo = @Param1
</cfquery>

In which case, the call to the procedure is no longer the first statement, and will fail without the EXEC. Seems obvious once I worked it out, but thought I should blog this in case I forget, or to help anyone else scratching their heads over that one.

Of course, the whole issue wouldn’t have arisen if we were using <cfstoredproc> instead, but that’s a separate issue!

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: