Convert a Stored Procedure to a Stored Function in MYSQL - Stack Overflow

admin管理员组

文章数量:1122846

Is it possible to change this stored procedure to a stored function? I created the procedure thinking it was a requirement for my coursework, when in fact I need a stored function.

I'd rather not have to start from the beginning. So I thought I'd ask here.

Essentially at the moment, it's creating a column output called 'Cell Line Priority' depending on the case statement, so if the cell line exists (Yes) = 'In Place'; No and grade 3 or 4 = High Priority; and if it's No and grade 1 or 2 = 'Low Priority'

DELIMITER $$
CREATE PROCEDURE Cell_Lines()

RETURNS VARCHAR(30)
DETERMINISTIC

BEGIN
    DECLARE cell_line_needed VARCHAR(30)
SELECT td.original_tissue_ref AS 'Tissue Reference', td.tissue_grade AS 'Tissue Grade', tt.tissue_type AS 'Tissue Type',
(CASE
WHEN td.cell_line_created = 'yes' THEN 'In Place'
WHEN td.cell_line_created = 'no' AND (td.tissue_grade = 3 OR td.tissue_grade = 4)
    THEN 'High Priority'
WHEN td.cell_line_created = 'no' AND (td.tissue_grade = 1 OR td.tissue_grade = 2)
    THEN 'Low Priority'
    END) AS 'Cell Line Priority'
        FROM tissue_data AS td
        JOIN tissue_type AS tt
        ON td.tissue_type = tt.tissue_type_id
    ORDER BY tt.tissue_type;
 END $$
 DELIMITER ;

Is it possible to change this stored procedure to a stored function? I created the procedure thinking it was a requirement for my coursework, when in fact I need a stored function.

I'd rather not have to start from the beginning. So I thought I'd ask here.

Essentially at the moment, it's creating a column output called 'Cell Line Priority' depending on the case statement, so if the cell line exists (Yes) = 'In Place'; No and grade 3 or 4 = High Priority; and if it's No and grade 1 or 2 = 'Low Priority'

DELIMITER $$
CREATE PROCEDURE Cell_Lines()

RETURNS VARCHAR(30)
DETERMINISTIC

BEGIN
    DECLARE cell_line_needed VARCHAR(30)
SELECT td.original_tissue_ref AS 'Tissue Reference', td.tissue_grade AS 'Tissue Grade', tt.tissue_type AS 'Tissue Type',
(CASE
WHEN td.cell_line_created = 'yes' THEN 'In Place'
WHEN td.cell_line_created = 'no' AND (td.tissue_grade = 3 OR td.tissue_grade = 4)
    THEN 'High Priority'
WHEN td.cell_line_created = 'no' AND (td.tissue_grade = 1 OR td.tissue_grade = 2)
    THEN 'Low Priority'
    END) AS 'Cell Line Priority'
        FROM tissue_data AS td
        JOIN tissue_type AS tt
        ON td.tissue_type = tt.tissue_type_id
    ORDER BY tt.tissue_type;
 END $$
 DELIMITER ;

• mysql
• stored-functions

Share Improve this question Follow asked Nov 21, 2024 at 12:10 CannonJacksCannonJacks 1977 bronze badges 9

• 1 Looks like you started (ie you have a returns statement in the published code) so I'm not confident that the published 'procedure' code is a valid start point. – P.Salmon Commented Nov 21, 2024 at 12:23
• 1 ??? Procedure declares a local variable... but it is used nowhere! Then it executes a query which returns many rows and saves it to output stream. And it contains RETURNS clause which is not legal in the stored procedure. Illogical and synthactically incorrect procedure cannot be converted to function. – Akina Commented Nov 21, 2024 at 12:23
• Also add table defintions, sample data and expected outcome as text. – P.Salmon Commented Nov 21, 2024 at 12:24
• 1 The published code returns a result set, a function cannot and can only return a scalar value the requirement to write a function does not square with the requirement to return a result set hence an x-y problem – P.Salmon Commented Nov 21, 2024 at 16:42
• 1 There's nothing that will automatically convert it, but you should be able to use copy and paste from the procedure to the function. – Barmar Commented Nov 21, 2024 at 17:32

 |  Show 4 more comments

1 Answer 1

Sorted by: Reset to default 1

The answer to your question is no — you can't "convert" a stored procedure to a stored function. They do different things, and they are coded differently.

You can, as you say, start from the beginning. Develop a new stored function. It may have some code in common with your stored procedure. But it will necessarily have differences.

本文标签： Convert a Stored Procedure to a Stored Function in MYSQLStack Overflow