CODEWITHSUNDEEP
mysqlarrays
Z R
Z R

Reputation: 121

MySQL: I want to pass an array to a function and see it iterate through each element of the array

I have the following code so far:

delimiter //
CREATE FUNCTION Iteration(InputArray CHAR) 
RETURNS DECIMAL
BEGIN
    RETURN 
    SELECT Max(IF(Stock = InputArray, ValueFrom, NULL)) AS Stock FROM DatabaseName.TableName LIMIT 1;
END 

delimiter ;

SET @Stocks = (SELECT ColumnName FROM DatabaseName.TableName LIMIT 1);
SELECT Iteration(@Stocks)FROM DatabaseName.TableName;

What I'm interested in doing is passing an array to the Iteration function, which would then be operated on by looking up the corresponding value in the DatabaseName database, spitting back out a corresponding decimal for each value in the input array-- in other words, one array in, another array out.

I think I instantiated everything correctly, but I keep getting the following error:

Error Code: 1305. FUNCTION applications.Iteration does not exist

For example, I have the variables AAA, BBB, CCC, etc, which are stock inventory codes. I want to pass an array of these variables to the procedure/function, and then go onto get back an array as well. In this case, it would be 1.7, 1.3, and 1.8.

There's a few questions I have:

  1. Does this make sense-- that is, can I pass an array to a function like this?
  2. If 1. is yes, am I passing the array to the function properly?
  3. Why am I getting this error?

Upvotes: 2

Views: 116

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521437

First, we need to nail down the logic you actually want for the SQL query. You want to take the MAX value of the ValueFrom column, but only for those records where the Stock is contained in the input array. This can be accomplished by doing a simple SELECT, along with a WHERE clause which filters out the non matching records, e.g.

SELECT ValueFrom
FROM DatabaseName.TableName
WHERE Stock IN ('stock1', 'stock2', 'stock3')

Of course, we will need to replace the list of stocks with the input array. I would use a stored procedure here:

CREATE PROCEDURE Iteration(IN inputArray VARCHAR(255))
BEGIN

  SET @sql = CONCAT('SELECT ValueFrom FROM DatabaseName.TableName WHERE Stock IN (', inputArray, ')');
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;

END
$$

DELIMITER ;

Usage:

SET @inputArray = '\'stock1\',\'stock2\',\'stock3\'';
CALL Iteration(@inputArray);

Upvotes: 1

Related Questions