Databases
StackOverflow2013
Postsdbo
POMySQL logic: different result for Stored Procedure vs Query with just body of Proceduce
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL logic: different result for Stored Procedure vs Query with just body of Proceduce
    primarykey
    data
    text
    <p>Ok so my stored procedure is reading variables wrong in its MySQL query. The results I need are obtained fine when I just use the body of the proc. I would like to make this a stored procedure. </p> <p>Here is the SP in SQLyog:</p> <pre><code>DELIMITER $$ USE `XXXXXXXXXXXXXXX`$$ DROP PROCEDURE IF EXISTS `getcusbyzip`$$ CREATE DEFINER=`XXXXXXXXXXXXXXX`@`%` PROCEDURE `getcusbyzip`(IN zipcode VARCHAR(30), IN radius VARCHAR(30)) BEGIN 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 &lt;radius ORDER BY distance ) AS RelevantCodes ON ( C.ZIPCODE = RelevantCodes.ZIPCODE ); END$$ DELIMITER ; </code></pre> <p>Now there is only 1 record that should be returned because I have only 1 customer added for now until I get this type issue resolved. Here is where I have a breakthrough (please don't laugh, I am new to SQL entirely :) ). </p> <p>I have tried changing the TYPE as well for the parameters. The zipcode broke after I changed it to an INT and I used this:</p> <pre><code>CALL getcusbyzip(08361,50) </code></pre> <p>and my resulting rows were all the rows in which "8361" were in the zipcode. Basically that is my only personal explanation as to why I received so many rows back.</p> <p>When I use this with both params set to VARCHAR type:</p> <pre><code>CALL getcusbyzip(08361,50) </code></pre> <p>or</p> <pre><code>CALL getcusbyzip('08361',50) </code></pre> <p>or</p> <pre><code>CALL getcusbyzip(08361,'50') </code></pre> <p>you get the point.</p> <p>I get 0 rows back when I should be getting 1 row back. </p> <p>So now I think Iknow what my problem is and I might actually ask it hopefully. What is causing my SP to read the input differently as a parameter than as if I just did a query and manually put the parameters into the query. Secondly how can I adjust my procedure so that the MySQL server reads my procedure just like my Query but with the parameters ofcoarse?</p> <p>Below is the query that works perfectly fine:</p> <pre><code>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 &lt;50 ORDER BY distance ) AS RelevantCodes ON ( C.ZIPCODE = RelevantCodes.ZIPCODE ) </code></pre> <p>EDIT: </p> <p>Another point to note is that when I change the variables to direct information in the SP(Stored Procedure) that does not fix it either so it has to be something I missing in MySQL procedure logic. I honestly do not understand why the results are different just because its in a stored procedure. </p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USMark
    1. USMark
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. CO`CALL getcusbyzip('08361',50)` works fine for me
      singulars
      1. POMySQL logic: different result for Stored Procedure vs Query with just body of Proceduce
      1. USJan S
    2. COdid you recreate the setup? Should I post sample table names? I have no clue why it is giving me mixed results.
      singulars
      1. POMySQL logic: different result for Stored Procedure vs Query with just body of Proceduce
      1. USMark
    3. COYes I did. I created the test tables, and ran the function and the stored procedure. You can try changing the declaration to `getcusbyzip(IN zipcode VARCHAR(30), IN radius INT)` instead of `getcusbyzip(IN zipcode VARCHAR(30), IN radius VARCHAR(30))`
      singulars
      1. POMySQL logic: different result for Stored Procedure vs Query with just body of Proceduce
      1. USJan S
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload