title: Variables within STORED Procedures
body: <p>I have a web application that calls many stored procedures when required.</p>
  
  <p>Within each stored procedure i have local variables declared within these stored procedures.</p>
  
  <p>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.</p>
  
  <p>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. </p>
  
  <p>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?</p>
  
  <p>Here is the stored proc :</p>
  
  <pre><code>        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 &gt; 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
  </code></pre>