title: How can I assign a variable with a prepared statement in a stored procedure?
body: <p>I've put together a simple stored procedure in which two parameters are passed through to make it more dynamic. I've done this with a prepared statement in the "First Two Digits and Count of Records" section.</p>
  
  <p>What I'm not sure of is if I can make the <code>SET vTotalFT</code> section dynamic with a prepared statement as well.</p>
  
  <p>At the moment I have to hard-code the table names and fields. I want my <code>vTotalFT</code> variable to be assigned based on a prepared dynamic SQL statement, but I'm not sure of the syntax. The idea is that when I call my procedure, I could tell it which table and which field to use for the analysis.</p>
  
  <pre><code>CREATE PROCEDURE `sp_benfords_ft_digits_analysis`(vTable varchar(255), vField varchar(255))
      SQL SECURITY INVOKER
  BEGIN
  
      -- Variables
      DECLARE vTotalFT int(11);
  
      -- Removes existing table
      DROP TABLE IF EXISTS analysis_benfords_ft_digits;
  
      -- Builds base analysis table
      CREATE TABLE analysis_benfords_ft_digits
      (
          ID int(11) NOT NULL AUTO_INCREMENT,
          FT_Digits int(11),
          Count_of_Records int(11),
          Actual decimal(18,3),
          Benfords    decimal(18,3),
          Difference Decimal(18,3),
          AbsDiff decimal(18,3),
          Zstat decimal(18,3),
          PRIMARY KEY (ID),
          KEY id_id (ID)
      );
  
      -- First Two Digits and Count of Records
      SET @s = concat('INSERT INTO analysis_benfords_ft_digits
                          (FT_Digits,Count_of_Records)
                              select substring(cast(',vField,' as char(50)),1,2) as FT_Digits, count(*) as Count_of_Records
                              from ',vTable,'
                              where ',vField,' &gt;= 10
                              group by 1');
  
      prepare stmt from @s;
      execute stmt;
      deallocate prepare stmt;
  
      SET vTotalFT = (select sum(Count_of_Records) from
                              (select substring(cast(Gross_Amount as char(50)),1,2) as FT_Digits, count(*) as Count_of_Records
                                  from supplier_invoice_headers
                                  where Gross_Amount &gt;= 10
                                  group by 1) a);
  
  
      -- Actual
      UPDATE analysis_benfords_ft_digits
      SET Actual = Count_of_Records / vTotalFT;
  
      -- Benfords
      UPDATE analysis_benfords_ft_digits
      SET Benfords = Log(1 + (1 / FT_Digits)) / Log(10);
  
      -- Difference
      UPDATE analysis_benfords_ft_digits
      SET Difference = Actual - Benfords;
  
      -- AbsDiff
      UPDATE analysis_benfords_ft_digits
      SET AbsDiff = abs(Difference);
  
      -- ZStat
      UPDATE analysis_benfords_ft_digits
      SET ZStat = cast((ABS(Actual-Benfords)-IF((1/(2*vTotalFT))&lt;ABS(Actual-Benfords),(1/(2*vTotalFT)),0))/(SQRT(Benfords*(1-Benfords)/vTotalFT)) as decimal(18,3));
  </code></pre>