Why does query give different result once put into stored procedure?

Question

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.

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

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.

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.

Answer 1

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

At the top of your sample code (not SP)

• Declare a variable called zipcode of type varchar(30)
• Declare a variable called radius of type 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.

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

Summary

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.