一旦放入存储过程，查询为什么会给出不同的结果? [英] Why does query give different result once put into stored procedure?

问题描述

我已阅读下面列出的几篇文章，以尝试找到我的问题的答案.他们俩似乎都有相同的问题.

I have read a few articles listed below to try and find an answer to my problem. They both seemed to have the same issue.

我看到了这一个，但我读了在MySQL文档中，默认情况下所有过程都是不确定的.

I saw this one but I read in the MySQL documentation that all procedures are by default non deterministic.

此与我的问题无关，因为它是SQL Server(不是MySQL).

This one doesn't have anything to do with my problem because its SQL Server (not MySQL).

这是我用来创建过程的查询:

Here is the query I am using to create my procedure:

CREATE PROCEDURE getcusbyzip(zipcode VARCHAR(30),radius VARCHAR(30))
SELECT C.CustomerName, C.MenuId
FROM Customers C
INNER JOIN (

SELECT ZIPCODE, ( 3959 * ACOS( COS( RADIANS( (

SELECT Z.LAT
FROM ZipCodes Z
WHERE Z.ZIPCODE =zipcode
LIMIT 0 , 1
) ) ) * COS( RADIANS( LAT ) ) * COS( RADIANS( LNG ) - RADIANS( (

SELECT Z.LNG
FROM ZipCodes Z
WHERE Z.ZIPCODE =zipcode
LIMIT 0 , 1
) ) ) + SIN( RADIANS( (

SELECT Z.LAT
FROM ZipCodes Z
WHERE Z.ZIPCODE =zipcode
LIMIT 0 , 1
) ) ) * SIN( RADIANS( LAT ) ) ) ) AS distance
FROM ZipCodes
HAVING distance <radius
ORDER BY distance
LIMIT 0 , 20
) AS RelevantCodes ON ( C.ZIPCODE = RelevantCodes.ZIPCODE )

现在，如果我在该查询之后运行此查询:

Now If I run this query after that one:

CALL getcusbyzip(08360,50)

我得到零行返回.但是，如果我在过程中像查询一样运行完全相同的语句，然后将参数放入这样:

I get zero rows returned. But if I run the exact same statement inside the procedure as just a query and put the params in like this:

SELECT C.CustomerName, C.MenuId
FROM Customers C
INNER JOIN (

SELECT ZIPCODE, ( 3959 * ACOS( COS( RADIANS( (

SELECT Z.LAT
FROM ZipCodes Z
WHERE Z.ZIPCODE =08360
LIMIT 0 , 1
) ) ) * COS( RADIANS( LAT ) ) * COS( RADIANS( LNG ) - RADIANS( (

SELECT Z.LNG
FROM ZipCodes Z
WHERE Z.ZIPCODE =08360
LIMIT 0 , 1
) ) ) + SIN( RADIANS( (

SELECT Z.LAT
FROM ZipCodes Z
WHERE Z.ZIPCODE =08360
LIMIT 0 , 1
) ) ) * SIN( RADIANS( LAT ) ) ) ) AS distance
FROM ZipCodes
HAVING distance <50
ORDER BY distance
LIMIT 0 , 20
) AS RelevantCodes ON ( C.ZIPCODE = RelevantCodes.ZIPCODE )

我得到了我想要的.答案只能是一行，但是为什么结果不在我的程序中却出现在我的脑海中，但是当我将它放在一个行中时却不会给我结果.

I get exactly what I wanted. The answer should only be one row but why is the result coming to me while not in a procedure but when I put it in one it will not give me the row.

我认为可能是因为我没有BEGIN和END，但是当我将其放在create行之后并将END放在最后一行之后时，它无法生成该过程.

I thought maybe it was because I did not have BEGIN and END but when I place that after the create line and END after the last line it fails to produce the procedure.

推荐答案

您的示例代码和存储过程代码彼此之间太不同了.

Your sample code and stored procedure code are too different from one another.

为了确定您的问题，您需要执行以下操作

In order to identify your problem, you need to do the following

在示例代码(不是SP)的顶部

• 声明一个名为zipchar的变量，类型为varchar(30)
• 声明一个名为radius的变量，类型为varchar(30)

然后，将其余示例代码修改为与存储过程完全相同.

Then, modify the rest of the sample code to be exactly the same as the stored procedure.

使用此方法，您将能够确定确切的问题.

You will be able to identify the exactly problem using this method.

如评论中所述，很可能是由于将varchars转换为数字所致.

As mentioned in the comments, it is likely due to the abuse of varchars being converted to numerics.

摘要

在示例代码的顶部声明并设置变量，以便其余示例与您的存储过程完全相同.然后，您可以开始确定问题.

Declare and set variables at the top of your sample code so that the rest of the sample is EXACTLY the same as your stored procedure. Then you can start to identify the problem.

这篇关于一旦放入存储过程，查询为什么会给出不同的结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！