0

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?

snicksnackpaddywhack91
  • 163
  • 2
  • 9
  • Can you please share the schema? – Imran Faruqi Apr 21 '20 at 04:03
  • @ImranFaruqi What would be the best way to share it with you? The schemas are sales and production. Sales: Customers, Orders, Order Items, Stores, and Staff Production: Categories, Products, Stocks, and Brands The relationships between the tables are all illustrated in that link at the top of my post for a more clear understanding Having typed this out, I just saw the Stocks table and instead of joining across three tables, I think i could join with just Products (for the product_name "string") and then joined to stocks "quantity" and "store_id". Am I on the right path? – snicksnackpaddywhack91 Apr 21 '20 at 04:15
  • You are close. I think provided answer will solve the issue. Let me know if you need any further help. By sharing schema, I meant to tell the names of the tables which you did. – Imran Faruqi Apr 21 '20 at 04:17
  • thanks so much! I'll give it a try! – snicksnackpaddywhack91 Apr 21 '20 at 05:51
  • You forgot to put WHERE clause through which you will filter out the product. – Imran Faruqi Apr 24 '20 at 20:56
  • I think that may have solved it. I added in "where p.product_name = productName" under my ON clause. Is that what you meant? I also changed my ORDER BY clause to "s.qunatity" as I believe that would sort them by max count? Am I correct in my assumption? – snicksnackpaddywhack91 Apr 24 '20 at 21:23
  • Absolutely! Although I would suggest you to use search for product name using "Like" keyword instead of "=". Also, off the topic, when you are searching on some column and it is your routine, make sure to index it for faster queries. – Imran Faruqi Apr 24 '20 at 21:48

1 Answers1

0

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.

Imran Faruqi
  • 663
  • 9
  • 19