How to turn this big query into a stored procedure

Question

How can I turn this big query into a stored procedure and should I? What would the benefit be?

SELECT * 
FROM user_items
LEFT JOIN items ON (items.item_id = user_items.item_id) 
INNER JOIN item_categories ON (item_categories.item_id = items.item_id)
INNER JOIN item_subcategories ON (item_subcategories.item_id = items.item_id)
INNER JOIN brands ON (brands.brand_id = items.item_brand)
INNER JOIN item_photos ON (item_photos.item_id = items.item_id)

INNER JOIN place_items ON (place_items.item_id = items.item_id)
INNER JOIN places ON (places.place_id = place_items.place_id)

WHERE user_items.user_id = :user_id

from the brands table I only need the brand_name
from the places table I only need the place_name

The way I'm doing it right now, I'm getting all columns from brands and places, so a friend of mine told me I should probably consider using stored procedures

all columns from items and all from categories/subcategories — Kaloyan Roussev, Feb 19 '14 at 11:27

score 3 · Accepted Answer · edited May 23 '17 at 10:26

3

If you want columns from brands and items tables only, you can do like below

"SELECT   brands.brand_name,places.place_name,
          user_items.*,items.*,item_categories .*,
          item_subcategories.*,item_photos.*,place_items.*
 FROM user_items
          LEFT JOIN items ON (items.item_id = user_items.item_id) 
          INNER JOIN item_categories ON (item_categories.item_id = items.item_id)
          INNER JOIN item_subcategories ON (item_subcategories.item_id = items.item_id)
          INNER JOIN brands ON (brands.brand_id = items.item_brand)
          INNER JOIN item_photos ON (item_photos.item_id = items.item_id)

          INNER JOIN place_items ON (place_items.item_id = items.item_id)
          INNER JOIN places ON (places.place_id = place_items.place_id)

          WHERE user_items.user_id = :user_id"

The use of stored procedure is to reuse a set of SQL statements . The performance of stored procedure would be as good as the SQL statements it contains.

A better approach for better readability of your code is to use ALIASES for table names.

When to use SQL Table Alias

edited May 23 '17 at 10:26

Community

1
1

answered Feb 19 '14 at 11:24

Mudassir Hasan

28,083
20
99
133

will this return * from items, categories, subcategories? – Kaloyan Roussev Feb 19 '14 at 11:28
thank you for the edit. what if I need more and more stuff from more tables, is it okay to keep going like this or should I use stored procedures (which seems like a scary concept) – Kaloyan Roussev Feb 19 '14 at 11:30
for getting all columns from a table you do `tableName.*` – Mudassir Hasan Feb 19 '14 at 11:30
you should not be scared of Stored procedure : ) ... its just a name for collection of one or more SQL statements. – Mudassir Hasan Feb 19 '14 at 11:31
is it okay that I dislike using Aliases? will people think down of my code? – Kaloyan Roussev Feb 19 '14 at 11:40
1

lolz..nothing like that buddy ... alias make your code easier to read , your fellow coders will sing parises of you fro making – Mudassir Hasan Feb 19 '14 at 12:58

score 1 · Answer 2 · edited May 23 '17 at 11:57

1

In my experience, stored procedures have been more trouble than they're worth, being inflexible and therefore more difficult to maintain than inline SQL, residing outside version control, and failing to provide much if any performance benefit. And in this case a stored routine doesn't seem necessary or beneficial, because your query doesn't demand an advanced feature, such as a cursor. For another discussion of advantages and disadvantages, see this post.

edited May 23 '17 at 11:57

Community

1
1

answered Feb 19 '14 at 11:31

Tim Burch

1,088
7
9

3

I disagree - mostly because there's absolutely no reason why a stored procedure should be *outside version control* - if it is, it's just your own laziness .... – marc_s Feb 19 '14 at 12:17
@marc_s Would you say that version control of MySQL stored procedures is easier or harder than version control of application code?--I have found it to be far more challenging, particularly with limited resources. Also, how about addressing the entirety of my answer, i.e., the other points? Finally, on the subject of "laziness," your grammar and punctuation are atrocious. – Tim Burch Feb 19 '14 at 13:40
1

As I said - I disagree - with everything you said, basically. I don't see version control being harder for stored procedures (and anything in your database) than source code, I disagree with the *being inflexible and more difficult to maintain* statement you made. And sorry for my grammar - English is **not** my first language – marc_s Feb 19 '14 at 13:44

score 0 · Answer 3 · answered Feb 19 '14 at 12:31

    DELIMITER $$
    DROP PROCEDURE IF EXISTS `ABC` $$
    CREATE PROCEDURE `ABC`(IN UID LONG)
    READS SQL DATA
    BEGIN
    SELECT * 
    FROM user_items
    LEFT JOIN items ON (items.item_id = user_items.item_id) 
    INNER JOIN item_categories ON (item_categories.item_id = items.item_id)
    INNER JOIN item_subcategories ON (item_subcategories.item_id = items.item_id)
    INNER JOIN brands ON (brands.brand_id = items.item_brand)
    INNER JOIN item_photos ON (item_photos.item_id = items.item_id)
    INNER JOIN place_items ON (place_items.item_id = items.item_id)
    INNER JOIN places ON (places.place_id = place_items.place_id)
    WHERE user_items.user_id = UID
    END $$
    DELIMITER ;



you can do like this and execute this and can call then :-

CALL ABC(1234);

where 1234 is user_id of user. Thank you

so I wite this instead of the SQL, and make no other changes? — Kaloyan Roussev, Feb 19 '14 at 13:19

How to turn this big query into a stored procedure

3 Answers3