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

    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:


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

    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
    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
    San Sebastian, Spain
    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))
    SELECT * FROM simpleblog_test.user WHERE username = pUsername;
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    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