使用cfstoredproc和cfquery的不同结果 [英] Different results with cfstoredproc and cfquery

问题描述

当我通过 cfstoredproc 执行存储过程时，我得到不同于通过 cfquery 。我传递相同的精确参数值到每个调用。而且，当我在SQL Studio中运行存储过程时，我得到正确的结果（与cfquery相同）。

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

以下是 cfstoredproc 呼叫

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

这里是 cfquery >

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

UPDATE

存储过程是巨大的（我继承的），所以我不打算在这里粘贴： http://pastebin.com/EtufPWXf

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

推荐答案

（从注释）

看起来它有可选参数。因此，您的 cfstoredproc 调用可能不会传递您认为的值。基于顺序，它看起来像是实际传递的值： @CityZipID，@Sic，@lastRank 。正如丹所提到的（我暗示），cfstoredproc对参数使用位置符号（ @dbVarName已弃用）。您需要以正确的顺序提供所有参数值 。

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.

更新：

FWIW，如果创建shell程序， cfstoredproc和cfquery实际上是调用具有不同参数/值的过程。 （见下文）。

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

如果你调用没有命名参数的过程，你肯定会看到一个区别@Dan建议ie exec rankingresults 8652，50，53 。 （我知道你说有没有变化，但你的测试可能只是一个错误。）

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 >

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)

这篇关于使用cfstoredproc和cfquery的不同结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！