Variables within STORED Procedures

Question

I have a web application that calls many stored procedures when required.

Within each stored procedure i have local variables declared within these stored procedures.

I expect these local variables, are specific to that particular stored procedure. So when this stored procedure executes, the local variables declared within the procedure, are specific to that stored procedure and should be dicarded once that procedure exits.

I have used the same names of local variables, within my stored procedures, but it seems that these variables are not being re-set when another stored proc is executed.

I am also using PREPARE STATEMENTS that execute a query, and after these statements are executed, i am deallocating them. Why am i having these issues of variables still not being re-set?

Here is the stored proc :

        CREATE DEFINER=`root`@`localhost` PROCEDURE `suggested_projects`(
               IN userName varchar(20),
               OUT projectCode int,
               OUT projectName text,
               OUT projectCreationDate DATETIME,
               OUT projectEndDate DATETIME,
               OUT projectStatus varchar(8),
               OUT noOf smallInt)
        BEGIN
        DECLARE
            l_cnt, l_occ int;

        DECLARE
            l_table_name varchar(30);

        DECLARE
            l_create,droptable, l_select_cnt, l_select_cnt2, l_select_cnt3, l_other_expertise, l_query1,
            l_delete_other, l_final_query longtext;

           set l_table_name = concat("tmp_rec_",userName);

           set @l_select_cnt = concat("SELECT count(1) into @l_cnt
                                       from information_schema.tables
                                      where table_schema = 'greptech'
                                      and table_name = '", l_table_name, "'");

           PREPARE stmt2 FROM @l_select_cnt;
           EXECUTE stmt2;
           DEALLOCATE PREPARE stmt2;

           if @l_cnt > 0 then
               set @droptable = concat("drop table ", l_table_name);

               PREPARE stmt1 FROM @droptable;
               EXECUTE stmt1;
               DEALLOCATE PREPARE stmt1;
           END IF;


           set @l_create = concat("CREATE TABLE ", l_table_name,
                               " SELECT a.expertise_desc
                                  from expertise a
                                 inner join users_expertise b
                                    on a.expertise_code = b.expertise_code
                                   and b.sp_user_name = '", userName , "'");

               PREPARE stmt3 FROM @l_create;
               EXECUTE stmt3;
               DEALLOCATE PREPARE stmt3;

           set @l_select_cnt2 = concat("SELECT other_expertise into @l_other_expertise
                                       from users_expertise
                                      where sp_user_name = '", userName, "' ",
                                      "and expertise_code = 'OTHER'");

           PREPARE stmt4 FROM @l_select_cnt2;
           EXECUTE stmt4;
           DEALLOCATE PREPARE stmt4;

           if (@l_other_expertise IS NOT NULL) then
                -- Example RESULT : INSERT INTO tmp_rec_n10000 VALUES('DWH'),('TESTING'),('ARCHITECTURE')
                set @l_query1 = concat("INSERT INTO ", l_table_name, " VALUES('", REPLACE(@l_other_expertise,',','\'),(\''), "')");

                PREPARE stmt5 FROM @l_query1;
                EXECUTE stmt5;
                DEALLOCATE PREPARE stmt5;
            END IF;

           set @l_delete_other = concat("DELETE from ", l_table_name,
                                       " where expertise_desc = 'Other'");

           PREPARE stmt6 FROM @l_delete_other;
           EXECUTE stmt6;
           DEALLOCATE PREPARE stmt6;

           set @l_final_query = concat("SELECT a.project_code as projectCode, a.project_name as projectName, a.project_creation_date as projectCreationDate, a.project_end_date as projectEndDate, a.project_status as projectStatus, count(c.project_code) as noOf from projects a inner join ", l_table_name, " b on a.project_name like concat(\"%\",trim(b.expertise_desc),\"%\") OR a.project_description like concat(\"%\",trim(b.expertise_desc),\"%\") left join project_ids c on c.project_code = a.project_code group by a.project_code");

           PREPARE stmt7 FROM @l_final_query;
           EXECUTE stmt7;
           DEALLOCATE PREPARE stmt7;

        END

Answer 1

You can declare local variables like this

DECLARE xname VARCHAR(5) DEFAULT 'bob';

A session variable can de declared like this

SET @var := 1

You should use local variables in your stored procedure. These will re removed and the end of the block they were defined.

Edit

Example stored procedure:

delimiter |
create procedure test_proc(myname varchar(100))
begin
    declare some_id int;
    select id into some_id
    from mytable
    where `name` = myname;

    select some_id;
end |
delimiter ;

Edit 2

drop procedure suggested_projects;

delimiter |

CREATE DEFINER=`root`@`localhost` PROCEDURE `suggested_projects`(
           IN userName varchar(20),
           OUT projectCode int,
           OUT projectName text,
           OUT projectCreationDate DATETIME,
           OUT projectEndDate DATETIME,
           OUT projectStatus varchar(8),
           OUT noOf smallInt)
    BEGIN
    DECLARE
        l_cnt, l_occ int;

    DECLARE
        l_table_name varchar(30);

    DECLARE
        l_create,droptable, l_select_cnt, l_select_cnt2, l_select_cnt3, l_other_expertise, l_query1,
        l_delete_other, l_final_query longtext;

       select concat("tmp_rec_",userName) into l_table_name;

       select concat("SELECT count(1) into @l_cnt
                                   from information_schema.tables
                                  where table_schema = 'greptech'
                                  and table_name = '", l_table_name, "'") 
                                  into l_select_cnt;

       set @query = l_select_cnt;
       PREPARE stmt2 FROM @l_select_cnt;
       EXECUTE stmt2;
       DEALLOCATE PREPARE stmt2;

       if l_cnt > 0 then
           select concat("drop table ", l_table_name) into droptable;

           set @query = droptable;
           PREPARE stmt1 FROM @query;
           EXECUTE stmt1;
           DEALLOCATE PREPARE stmt1;
       END IF;


       select concat("CREATE TABLE ", l_table_name,
                           " SELECT a.expertise_desc
                              from expertise a
                             inner join users_expertise b
                                on a.expertise_code = b.expertise_code
                               and b.sp_user_name = '", userName , "'")
                               into l_create;

           set @query = l_create;
           PREPARE stmt3 FROM @query;
           EXECUTE stmt3;
           DEALLOCATE PREPARE stmt3;

       select concat("SELECT other_expertise into @l_other_expertise
                                   from users_expertise
                                  where sp_user_name = '", userName, "' ",
                                  "and expertise_code = 'OTHER'")
                                  into l_select_cnt2;

       set @query = l_select_cnt2;
       PREPARE stmt4 FROM @query;
       EXECUTE stmt4;
       DEALLOCATE PREPARE stmt4;

       if (@l_other_expertise IS NOT NULL) then
            -- Example RESULT : INSERT INTO tmp_rec_n10000 VALUES('DWH'),('TESTING'),('ARCHITECTURE')
            set @l_query1 = concat("INSERT INTO ", l_table_name, " VALUES('", REPLACE(@l_other_expertise,',','\'),(\''), "')");

            PREPARE stmt5 FROM @query;
            EXECUTE stmt5;
            DEALLOCATE PREPARE stmt5;
        END IF;

       select concat("DELETE from ", l_table_name,
                                   " where expertise_desc = 'Other'")
                                   into l_delete_other;

       set @query = l_delete_other;
       PREPARE stmt6 FROM @query;
       EXECUTE stmt6;
       DEALLOCATE PREPARE stmt6;

       select  concat("SELECT a.project_code as projectCode, a.project_name as projectName, a.project_creation_date as projectCreationDate, a.project_end_date as projectEndDate, a.project_status as projectStatus, count(c.project_code) as noOf from projects a inner join ", l_table_name, " b on a.project_name like concat(\"%\",trim(b.expertise_desc),\"%\") OR a.project_description like concat(\"%\",trim(b.expertise_desc),\"%\") left join project_ids c on c.project_code = a.project_code group by a.project_code")
              into l_final_query;

       set @query = l_final_query;
       PREPARE stmt7 FROM @query;
       EXECUTE stmt7;
       DEALLOCATE PREPARE stmt7;

    END; |

delimiter ;

Answer 2

I only had this issue with temporary tables used by stored procedures called on the same db connection. Temp tables are of course shared for this connection:

This is the solution I have used, drop before every create:

drop table if exists t_tempTable;
create temporary table t_tempTable(.

.

The prepared statements are global also, see here: http://dev.mysql.com/doc/refman/5.0/en/local-variable-scope.html