asked on

MySQL stored procedure returning null values

I've been doing this a while with Microsoft SQL but this is my first foray into MySQL and I'm trying to figure it all out and am stuck on a simple store procedure. All I'm doing is trying to pull a list of active state abbreviations and state names from a table. My input parameter is a boolean named active. There are two output parameters, both varchar, named st_abbr and state. Depressingly enough, if I use a simple select, it works fine - I only hose it when I go to the sproc.

Using phpmyadmin, when I run the sproc, I get the rows but no data - just a list of nulls in both columns. Some of the things I've seen seem to suggest I need to assign the values I'm pulling from the table into others and export those. Most everything else suggests it's just simple SQL but I'm missing something and can't see what - so any shove in the right direction would be appreciated.

All relevant code is shown below - thanks very much in advance for any help.

/*TABLE STRUCTURE */
    CREATE TABLE 'tbl_States' (
      'id' int(11) unsigned NOT NULL auto_increment,
      'st_abbr' varchar(50) default NULL,
      'state' varchar(50) default NULL,
      'active' tinyint(1) 1 NULL,
      PRIMARY KEY ('id')
    );

 /* PROCEDURE STRUCTURE */
    USE WEB_EVENTS;

    DELIMITER $$

    CREATE PROCEDURE 'sp_GetStatesList'
    (
    IN 'active' BOOLEAN(1),
    OUT 'st_abbr' VARCHAR(50),
    OUT 'state' VARCHAR(50)
    )

    SQL SECURITY DEFINER
    LANGUAGE SQL
    NOT DETERMINISTIC

    BEGIN
    SELECT st_abbr, state FROM tbl_States WHERE active = active;
    END $$

    DELIMITER ;
    /* CALL STATEMENT */

    CALL sp_GetStatesList(1,@st_abbr, @state);

Open in new window

arnold

Select * from tbl_states
What do you get?

Usually, you want to avoid confusion and use distinct names for variables such that there is no way they could be confused with column names.

Why are you using quotes around your variable definitions?

saabStory

ASKER

Typically, you'd get the id, a two-letter state abbreviation, the full name of the state and the active flat. I only went for everything just to simplify things. Once i got it to work, I'd back down to select st_abbr, state where active = <active input parameter>.

The quotes I didn't really use - I actually built everything in phpmyadmin. I found another post where someone had presented their table, query and all in that way and it seemed a better way to show everything than what I had at the time.

ASKER CERTIFIED SOLUTION

arnold

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

saabStory

ASKER

Almost, but it put me on the right track. I changed the active to activestatus as you suggested but still no joy. Then, just for giggles, I did the same thing to the outputs - changed to st_abbrout and statesout respectively and that worked. So apparently it was getting confused in a lot of different ways.

Thanks for the help and the push in the right direction.