Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Regular Coder
    Join Date
    Jan 2014
    Posts
    130
    Thanks
    17
    Thanked 6 Times in 6 Posts

    Stored procedure to build address

    I have a database where my data is normalized, i.e. cities, states, and postal codes all have a dedicated table and anytime I reference them I use a foreign key value that links to their respective ID.

    Recently, I found out about sprocs. I am currently using it to store my encryption key like this:
    Code:
    DELIMITER $$
    CREATE DEFINER=`root`@`localhost` PROCEDURE `aes_key`()
    BEGIN
       SELECT '[my key goes here]' AS `key`;
    END$$
    DELIMITER ;
    And I figured that I could do the same to fetch the values found in the foreign keys, since anytime I need to get the address information I always need the city name, state abbreviation, and postal code at the same time. So I tried using the following:
    Code:
    DELIMITER $$
    CREATE DEFINER=`root`@`localhost` PROCEDURE `build_address`(
       IN city_id INT,
       IN state_id INT,
       IN postal_code_id INT
    )
    BEGIN
      SELECT
          (SELECT `city` FROM `city` WHERE `city_id` = city_id LIMIT 1),
          (SELECT `abbreviation` FROM `state` WHERE `state_id` = state_id LIMIT 1),
          (SELECT `postal_code` FROM `postal_code` WHERE `postal_code_id` = postal_code_id LIMIT 1);
    END$$
    DELIMITER ;
    But this returns the first value in the table(s) every time, no matter what value I pass through the parameter(s).

  2. #2
    Regular Coder
    Join Date
    Jan 2014
    Posts
    130
    Thanks
    17
    Thanked 6 Times in 6 Posts
    I've changed the code in between the BEGIN and END to:
    Code:
    BEGIN
        SELECT
            `city`.`city`,
            `state`.`abbreviation`,
            `postal_code`.`postal_code`
        FROM
            `city`,
            `state`,
            `postal_code`
        WHERE
            `city`.`city_id` = city_id AND `state`.`state_id` = state_id AND `postal_code`.`postal_code_id` = postal_code_id;
    END
    And now its working.


 

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •