Declaring and setting variables to test difference between SP and query

Question

I am trying to solve another problem here. I was told by a member that in order to find an answer to my problem I needed to change the query to match the SP. In order to do that I was told to declare variables so I am using variables like I am in the SP. my problem is I have read so much my eyes are hurting and I cannot figure out how to declare friggin variables.

Please help!!

This is what I have read so far:

resource 1, resource 2, resource 3, resource 4, resource 5

and I could go on an on. I am literally stuck. I am new to SQL and this is bugging me out. I do not have to use a SP but I would really like to so I can learn the dos and donts and hopefully learn enough to help others.

DECLARE zipcode VARCHAR(30)
DECLARE radius VARCHAR(30)
SET zipcode = 08360
SET radius = 50

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
) AS RelevantCodes ON ( C.ZIPCODE = RelevantCodes.ZIPCODE )

I have tried so many combinations from what I have seen on the internet that I am starting to think its just too late in the night to do anything more. If you take a look at some of the resources I looked at you will see where I saw the difference between with '@' and without.

Answer 1

If you want to create a stored proc, you'll need to use the proper syntax.

See: http://dev.mysql.com/doc/refman/5.5/en/create-procedure.html
Note that inside the proc body you'll need to terminate each and every statement with a ;.

Because you'll need to let MySQL know when the body proc ends and you'll need to redeclare the delimiter into something that not a ;. This will prevent MySQL from interpreting your stored proc after the first ';', but instead listen all the way up to the $$.

Don't forget to revert the delimiter back to the default after you're done inputting the function.

SET DELIMITER $$

CREATE PROCEDURE QueryCustomerMenu(IN zipcode VARCHAR(30), IN radius VARCHAR(30))
BEGIN

DECLARE somevar VARCHAR(20);
SET somevar = '456';

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
) AS RelevantCodes ON ( C.ZIPCODE = RelevantCodes.ZIPCODE );

END $$

SET DELIMITER ;

Now you can call the stored proc with:

CALL QueryCustomerMenu('09210','20');

And it will return a resultset with the CustomerName and MenuID's.