How to assign a variable using a prepared statement in a stored procedure?

Question

I put together a simple stored procedure in which two parameters are passed in order to make them more dynamic. I did this with the prepared expression in the section "The first two digits and the number of entries."

I'm not sure that I can make the dynamic SET vTotalFT dynamic using a prepared statement.

At the moment, I have to hardcode the names and fields of the table. I want my vTotalFT variable vTotalFT be assigned based on a prepared dynamic SQL statement, but I'm not sure about the syntax. The idea is that when I call my procedure, I can tell her which table and which field to use for analysis.

 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,' >= 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 >= 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))<ABS(Actual-Benfords),(1/(2*vTotalFT)),0))/(SQRT(Benfords*(1-Benfords)/vTotalFT)) as decimal(18,3)); 

Answer 1

First, to use dynamic table / column names, you need to use the row / "Prepared expression" , like your first query for @s . Then, to get the return value from COUNT() inside the query, you will need to use SELECT .. INTO @vTotalFT .

The following is required:

 SET @vTotalFTquery = CONCAT('(select sum(Count_of_Records) INTO @vTotalFT from (select substring(cast(', vField, ' as char(50)),1,2) as FT_Digits, count(*) as Count_of_Records from ', vTable, ' where ', vField, ' >= 10 group by 1) a);'); PREPARE stmt FROM @vTotalFTquery; EXECUTE stmt; DEALLOCATE PREPARE stmt; 

Please note: the variable name has changed from vTotalFT to @vTotalFT . It seems to work without @ . Also, the @vTotalFT variable @vTotalFT not work if declared outside / before the request, so if you encounter an error or an empty result, this may be the reason.

Answer 2

 SELECT CONCAT ( 'SELECT DATE(PunchDateTime) as day , ' ,GROUP_CONCAT('GROUP_CONCAT(IF(PunchEvent=', QUOTE(PunchEvent), ',PunchDateTime,NULL)) AS `', REPLACE(PunchEvent, '`', '``'), '`') ,' FROM tbl_punch GROUP BY DATE(PunchDateTime) ORDER BY PunchDateTime ASC ' ) INTO @sql FROM ( SELECT DISTINCT PunchEvent FROM tbl_punch ) t; PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;