user1236443 user1236443 - 3 months ago 7
MySQL Question

How can I assign a variable with a prepared statement in a stored procedure?

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.

What I'm not sure of is if I can make the

SET vTotalFT
section dynamic with a prepared statement as well.

At the moment I have to hard-code the table names and fields. I want my
vTotalFT
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.

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

First, to use dynamic table/column names, you'll need to use a string/Prepared Statement like your first query for @s. Next, to get the return-value from COUNT() inside of the query you'll need to use SELECT .. INTO @vTotalFT.

The following should be all you need:

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 doesn't seem to work without the @. And also, the variable @vTotalFT won't work when declared outside of/before the query, so if you encounter an error or empty results that could be a cause.

Comments