CODEWITHSUNDEEP
phpmysqlstored-procedures
yendrrek
yendrrek

Reputation: 331

MySQL Stored procedure with multiple SELECT FROM statements from the same table

I have a table called "thumb" with relative paths to images and other data related with the images like their titles and descriptions. Each page of the website I'm working on represents different category of the images. For now, I have separate stored procedures for each page(category) to select different images according to their IDs. Is it possible to have one stored procedure for each page(category) which would select different images or do I need to create separate stored procedures for each range of images?

Now it is like this: Procedure for the 'All works' page:

BEGIN
SELECT * FROM thumb ORDER BY id ASC;
END

Procedure for "Paintings":

BEGIN
SELECT * FROM thumb WHERE id BETWEEN 12 AND 15 ORDER BY id ASC;
END

Procedure for "Drawings":

BEGIN
SELECT * FROM thumb WHERE id BETWEEN 16 AND 19 ORDER BY id ASC;
END

Etc.

Can a stored procedure listen to where the calling comes from and through if statements decide which SELECT * FROM statement to apply?

Upvotes: 1

Views: 178

Answers (1)

Akina
Akina

Reputation: 42632

You need something like

CREATE PROCEDURE get_images (IN pagetype TEXT)
SELECT * 
FROM thumb 
WHERE CASE pagetype WHEN 'Paintings' THEN id BETWEEN 12 AND 15
                    WHEN 'Drawings'  THEN id BETWEEN 16 AND 19 
                                     ELSE TRUE
                                     END
ORDER BY id ASC;

Upvotes: 1

Related Questions