Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2010
    Posts
    5

    Unanswered: Newbie: How to pass parameter in stored procedure?

    Hi! First post here. I'm trying to learn stored procedures but I cannot get my first procedure to work as expected. I have the following to find a user with a matching username:

    DELIMITER $$

    CREATE DEFINER=`root`@`localhost` PROCEDURE `new_routine`(IN userName VARCHAR(45))
    BEGIN
    SELECT * FROM simpleblog_test.user WHERE username = userName;
    END

    When I call this procedure with

    CALL new_routine('user1');

    I get all users as a result, not only the single user that I'm supposed to get back. However, if I just make a sql query like below:

    SELECT * FROM simpleblog_test.user WHERE username = 'user1';

    I get a single user back as supposed. Can anyone tell what's wrong in my stored procedure?

    Thanks for help!

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Column names in MySQL are case insensitive.
    Column, index, stored routine, and event names are not case sensitive on any platform, nor are column aliases.
    So, both of "username" and "userName" in the query in the procedure
    might be interpreted as a column name in simpleblog_test.user table, precedent over IN userName parameter.
    SELECT * FROM simpleblog_test.user WHERE username = userName;

  3. #3
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    What I typically do when writing stored procedures is define the name of each parameter with a leading p in order to avoid confusing with the same column in the table.

    CREATE DEFINER=`root`@`localhost` PROCEDURE `new_routine`(IN pUsername VARCHAR(45))
    BEGIN
    SELECT * FROM simpleblog_test.user WHERE username = pUsername;
    END
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

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