Creating a Stored Function/ Procedure in MySQL- New User

Question

Using the data from this site: https://www.sqlservertutorial.net/sql-server-sample-database/

I am given the prompt

Create a stored function called findStoreByProduct() that will take a string as input, and return a store_id. The store_id relates to the store with the largest stock the product identified in the input string.

Can someone first elaborate what the difference is between stored functions and stored procedures? I'm still a bit lost on that. I've gone through other stackoverflow posts regarding the same issue but am still drawing a blank.

Then can someone aid me in understanding how/where to start? My first thought would be to link the three tables (products, order items, and orders) using sub queries but apparently it's supposed to be more basic than that.

@Imran Faruqi

I thought I had managed to figure it out with your help but I think there is something wrong with my code because I keep getting the same output despite my select statements.

 DELIMITER //
CREATE function findStoreByProduct (product_name VARCHAR(300))
RETURNS INT DETERMINISTIC
BEGIN
DECLARE storeID INT;
DECLARE storeQuantity INT;
SELECT s.store_id from stocks as s
    INNER JOIN
        products AS p
        ON s.product_id = p.product_id
        ORDER BY p.product_id DESC
        LIMIT 1
        INTO storeID;
RETURN(storeID);
END //
SELECT findStoreByProduct("Trek XM700+ - 2018");

Despite what I put into the function, I always get the same result "1". Any thoughts?

Answer 1

This SO Post defines Stored Procedures vs Functions.

Then can someone aid me in understanding how/where to start?

First of all, you need to create an scalar function which will return only one value i.e. integer value.

1. Find the product_id via product name (provided in the function parameters).

2. Find the store with the largest quantity followed by product_id acquired above. You can achieve that using aggregate functions (here MAX() would be used). Make sure to group by product_id

Another strategy:

You can make a join between these two tables and use WHERE clause to match the product name (provided in function parameters) as well as utilize aggregate function suggested above along with GROUP BY clause.