Return value from MySQL stored procedure
Solution 1
your stored procedure is a little over-engineered for my liking - keep it simple :)
MySQL
drop table if exists tags;
create table tags
(
tag_id int unsigned not null auto_increment primary key,
name varchar(255) unique not null
)
engine=innodb;
drop procedure if exists insert_tag;
delimiter #
create procedure insert_tag
(
in p_name varchar(255)
)
proc_main:begin
declare v_tag_id int unsigned default 0;
if exists (select 1 from tags where name = p_name) then
select -1 as tag_id, 'duplicate name' as msg; -- could use multiple out variables...i prefer this
leave proc_main;
end if;
insert into tags (name) values (p_name);
set v_tag_id = last_insert_id();
-- do stuff with v_tag_id...
-- return success
select v_tag_id as tag_id, 'OK' as msg;
end proc_main #
delimiter ;
PHP
<?php
ob_start();
try{
$conn = new mysqli("localhost", "foo_dbo", "pass", "foo_db", 3306);
$conn->autocommit(FALSE); // start transaction
// create the tag
$name = 'f00';
$sql = sprintf("call insert_tag('%s')", $conn->real_escape_string($name));
$result = $conn->query($sql);
$row = $result->fetch_array();
$result->close();
$conn->next_result();
$tagID = $row["tag_id"]; // new tag_id returned by sproc
if($tagID < 0) throw new exception($row["msg"]);
$conn->commit();
echo sprintf("tag %d created<br/>refresh me...", $tagID);
}
catch(exception $ex){
ob_clean();
//handle errors and rollback
$conn->rollback();
echo sprintf("oops error - %s<br/>", $ex->getMessage());
}
// finally
$conn->close();
ob_end_flush();
?>
Solution 2
Stored PROCEDURES
can return a resultset. The last thing you SELECT
in a stored procedure is available as a resultset to the calling environment.. Stored FUNCTIONS
can return only a single result primitive.
You may also mark your parameters as INOUT
parameters.
Solution 3
If you want this:
DECLARE doesTagExist BOOL;
SET doesTagExist = CheckTagExist('str');
then you should use functions:
DELIMITER //
CREATE FUNCTION CheckTagExists(
tagName VARCHAR(255)
)
BEGIN
DECLARE doesTagExist BOOL;
-- Check if tag exists
SELECT
EXISTS(
SELECT
*
FROM
tags
WHERE
tags.NAME = tagName
)
INTO
doesTagExist;
RETURN doesTagExist;
END //
DELIMITER ;
Admin
Updated on June 04, 2020Comments
-
Admin almost 4 years
So I've finally decided to get around to learning how to use stored procedures, and although I do have them working, I'm unsure if I'm doing it correctly - aka. the best way. So here's what I've got.
Three procedures: TryAddTag, CheckTagExists, and AddTag.
TryAddTag is the procedure that is my intermediary between other code (eg. PHP, etc...) and the other two procedures, so this is the one that gets called.
TryAddTagDELIMITER // CREATE PROCEDURE TryAddTag( IN tagName VARCHAR(255) ) BEGIN -- Check if tag already exists CALL CheckTagExists(tagName, @doesTagExist); -- If it does not exist, add it IF @doesTagExist = FALSE THEN CALL AddTag(tagName); END IF; END // DELIMITER ;
AddTagDELIMITER // CREATE PROCEDURE AddTag( IN tagName VARCHAR(255) ) BEGIN INSERT INTO tags VALUES( NULL, tagName ); END // DELIMITER ;
CheckTagExistsDELIMITER // CREATE PROCEDURE CheckTagExists( IN tagName VARCHAR(255), OUT doesTagExist BOOL ) BEGIN -- Check if tag exists SELECT EXISTS( SELECT * FROM tags WHERE tags.NAME = tagName ) INTO doesTagExist; END // DELIMITER ;
My problems stem from this and use of @doesTagExist.-- Check if tag already exists CALL CheckTagExists(tagName, @doesTagExist);
Is the the correct way to use one of these variables? And/or, how can I use a DECLARE'd variable to store the result of CheckTagExists within TryAddTag? I expected something along the lines of
... DECLARE doesTagExist BOOL; SET doesTagExist = CheckTagExist('str'); ...
or something like that...