1

When I execute a stored proc via cfstoredproc, I am getting a different result than calling that stored proc via cfquery. I am passing in the same exact parameter values to each call. And also, when I run the stored proc in SQL Studio, I get the correct results (same as the cfquery).

Here's the cfstoredproc call

<cfstoredproc datasource="#request.mainDSN#" debug="#request.debug#" procedure="rankingresults">
   <cfprocparam type="in" value="8652" CFSQLType="CF_SQL_INTEGER">
   <cfprocparam type="in" value="50" CFSQLType="CF_SQL_INTEGER">
   <cfprocparam type="in" value="53" CFSQLType="CF_SQL_INTEGER">
   <cfprocresult name="local.listing">
</cfstoredproc>

Here is the cfquery call

<cfquery datasource="#request.mainDSN#" name="rankings">
   EXEC rankingresults
    @CityZipId = 8652,
    @distance = 50,
    @sic = 53
</cfquery>

The results are completely different. It's not even close. I've been banged my head over this for several hours, and I can't figure out why it is doing what it is doing.

UPDATE

The stored proc is massive (and one that I inherited), so I'm not going to paste it all here: http://pastebin.com/EtufPWXf

RHPT
  • 2,560
  • 5
  • 31
  • 43
  • 1
    please include the stored proc. Along with the results from each call. – Dave Alperovich Jan 24 '13 at 17:32
  • 1
    Post the body/source of the actual stored procedure, please. – Shawn Holmes Jan 24 '13 at 17:32
  • 1
    Does the procedure have any optional parameters? – Leigh Jan 24 '13 at 17:36
  • 1
    Has the procedure changed recently? it's possible that cfstoredproc is running a cached version where cfquery is not. I've seen that with PL/SQL. http://stackoverflow.com/questions/2951244/no-statement-parsed-and-wrong-number-or-types-of-arguments-cfstoredproc – genericHCU Jan 24 '13 at 17:39
  • 3
    cfstoredproc uses postional parameters. What happens if you run "exec rankingresults 8652, 50, 53" in cfquery? – Dan Bracuk Jan 24 '13 at 17:51
  • Have you run trace inside the proc call to see what SQL string is actually getting executed? – commadelimited Jan 24 '13 at 18:07
  • @DanBracuk no difference. – RHPT Jan 24 '13 at 18:07
  • @commadelimited H0w does one do that? – RHPT Jan 24 '13 at 18:08
  • Fire up the MS SQL Profiler and/or enable tracing in your CF datasource and check the log file output. – Leigh Jan 24 '13 at 18:11
  • 2
    @RHPT - Looks like it does have optional parameters. So your `cfstoredproc` call may not be passing in the values you think it is. Based on the order, it looks like it is actually passing in values for: `@CityZipID, @Sic, @lastRank`. As mentioned, cfstoredproc uses positional notation, so you need to supply all of the parameter values *in the correct order*. – Leigh Jan 24 '13 at 18:14
  • @Leigh That was it! I can't believe I missed that. – RHPT Jan 24 '13 at 18:24
  • if you run "exec rankingresults 8652, 50, 53" directly against your db (outside of coldfusion), do your results resemble anything you got from ColdFusion? – Dan Bracuk Jan 24 '13 at 18:24
  • @DanBracuk The problem, like you are implying, is that the positioning of my parameters were incorrect. Thanks! – RHPT Jan 24 '13 at 18:35

2 Answers2

12

(From the comments)

Looks like it does have optional parameters. So your cfstoredproc call may not be passing in the values you think it is. Based on the order, it looks like it is actually passing in values for: @CityZipID, @Sic, @lastRank. As Dan mentioned (and I hinted at), cfstoredproc uses positional notation for parameters (@dbVarName is deprecated). You need to supply all of the parameter values in the correct order.

Update:

FWIW, if you create a shell procedure you would see the cfstoredproc and cfquery are actually invoking the procedure with different parameters/values. (See below).

You would definitely see a difference in results if you invoked the procedure without the named parameters as @Dan suggested ie exec rankingresults 8652, 50, 53. (I know you said there was "no change", but there was probably just an error in your test).

CFSTOREDPROC

@ATTRCODES|@CITYZIPID|@DISTANCE|@HASURL |@ISFEATURED |@LASTRANK|@PHOTOCOUNT|@REVIEWCOUNT |@SIC|@SICBUDGETIDS 
(nothing)| 8652| (nothing)| (nothing)| (nothing)| 53| (nothing)| (nothing)| 50| (nothing)

CFQUERY

@ATTRCODES|@CITYZIPID|@DISTANCE|@HASURL |@ISFEATURED |@LASTRANK|@PHOTOCOUNT|@REVIEWCOUNT |@SIC|@SICBUDGETIDS 
(nothing)| 8652| 50| (nothing)| (nothing)| 0| (nothing)| (nothing)| 53| (nothing)
Leigh
  • 28,765
  • 10
  • 55
  • 103
0

If you run this directly on the sql server how many results does it return? You could be returning multiple results that could explain the difference in behavior.

Tim Cunningham
  • 329
  • 1
  • 7