0

I have the following mySQL (v5.7) Stored Procedure which works great (it calculates the average of the most recent 25 rows):

CREATE DEFINER=`SqueezeOJ`@`%` PROCEDURE `NVG_SMA25`(
    IN  myDate DATE,
    OUT mySMA25 DECIMAL(8,2)
)
BEGIN
    SELECT ROUND(AVG(ClosingPrice), 2) INTO mySMA25 FROM NVG
    WHERE TradeDate IN (SELECT * FROM (SELECT DISTINCT TradeDate 
                          FROM NVG 
                         WHERE TradeDate <= myDate
                         ORDER BY TradeDate DESC LIMIT 25) as calculating);
END

I'd like to parameterize the LIMIT 25 to LIMIT X - where I can pass X into the procedure, but everything I try leads to errors.

Can someone point me in the right direction?

Thanks, Jason

SqueezeOJ
  • 441
  • 7
  • 17
  • 1
    You can't parameterize it. The SQL syntax requires a literal number there. Procedures are overkill in most cases especially trivial ones like this. Window functions in later versions mysql or mariadb versions make this query easier – danblack Nov 14 '20 at 22:44
  • There's nothing 'procedural' as such here, so why not simply use a query – Strawberry Nov 15 '20 at 03:20
  • Perhaps there's a better way? Basically, I'm downloading stock prices every night and then calculating several moving averages on them (25, 50, 100). I'd like to be able to parameterize the name of the stock table and the length of time over which to run the moving average. I'm totally open to suggestions. Thanks! – SqueezeOJ Nov 15 '20 at 14:09

1 Answers1

0

13.2.9 SELECT Statement

...

  • The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants, with these exceptions:

    • Within prepared statements, LIMIT parameters can be specified using ? placeholder markers.

    • Within stored programs, LIMIT parameters can be specified using integer-valued routine parameters or local variables.

...

See dbfiddle.

wchiquito
  • 16,177
  • 2
  • 34
  • 45
  • As I understand it, mySQL version 5.7 does not "yet support 'LIMIT & IN/ALL/ANY/SOME subquery." (https://stackoverflow.com/questions/17892762/mysql-this-version-of-mysql-doesnt-yet-support-limit-in-all-any-some-subqu) – SqueezeOJ Nov 15 '20 at 14:07