Return value from MySQL stored procedure

Question

So, I finally decided to learn how to use stored procedures, and although I have their work, I'm not sure if I am doing this correctly. the best way. So here is what I have.

Three procedures: TryAddTag, CheckTagExists and AddTag.

TryAddTag is a procedure that is my intermediary between other code (like PHP, etc.) and two other procedures, so this is the call that is called.

TryAddTag

DELIMITER // 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 ; 

Addtag

 DELIMITER // CREATE PROCEDURE AddTag( IN tagName VARCHAR(255) ) BEGIN INSERT INTO tags VALUES( NULL, tagName ); END // DELIMITER ; 

CheckTagExists

 DELIMITER // 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 concerns stem from this and using @doesTagExist.

 -- Check if tag already exists CALL CheckTagExists(tagName, @doesTagExist); 

Is one of these variables used correctly? And / or, how can I use the DECLARE'd variable to store the result of CheckTagExists in TryAddTag? I was expecting something like

 ... DECLARE doesTagExist BOOL; SET doesTagExist = CheckTagExist('str'); ... 

or something like that...

Answer 1

Your stored procedure has been slightly redesigned to 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(); ?> 

Answer 2

A PROCEDURES can return a result set. The last thing you SELECT in a stored procedure is available as a result set for the calling environment. . A FUNCTIONS can only return one result primitive.
You can also mark your parameters as INOUT parameters .

Answer 3

If you want to:

 DECLARE doesTagExist BOOL; SET doesTagExist = CheckTagExist('str'); 

then you should use the 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 ; 

Answer 4

 DECLARE doesTagExist BOOL; SET CheckTagExist('str',doesTagExist); 

is the right way to do this using only storage procedures. There are no regular return values.