Mysql Stored Procedure cant return value

Dear all,

here is my script,

1st Question :

CREATE DEFINER = 'root'@'localhost' PROCEDURE `HelloWorld`(out p_text VARCHAR(30))
   BEGIN
    set @p_text = 'Hello';
END;

//at PHP script
// db_query is the function to connect to DB

$res_111 = db_query("call HelloWorld(@a);", 'S');

final "$res_111" no output return; also no error.

May i know how to write it with return the output?


Question 2 :

//store PROCEDURE  code

CREATE DEFINER = 'root'@'localhost' PROCEDURE `sp_select`(IN uid INTEGER(11), OUT result VARCHAR(50))
 BEGIN            
    SELECT username from admin where admin_index =uid into @result;    
    select @result;
END;

//at PHP script
// db_query is the function to connect to DB

$res_111 = db_query("call sp_select(3,@a);", 'S');

final "$res_111" no output return; also no error.

But right will return data from DB?

Any one how to write it?

LVL 1
sonny_j81Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

virmaiorCommented:
try this:

CREATE DEFINER = 'root'@'localhost' PROCEDURE `sp_select`(IN uid INTEGER(11), OUT result VARCHAR(50))
 BEGIN            
    SELECT username from admin where admin_index =uid into @result;    
    RETURN @result;
END;
0
sonny_j81Author Commented:
Dear sir ,
At mysql only function can use return command, other i think cant support it.

still got any idea to do it

urgent


thank q
ffrom

sonny
0
virmaiorCommented:
well switch and use a function...
you are asking for something to return a value.


alternately

add a query AFTER that query

'SELECT @result'
0
sonny_j81Author Commented:
Dear virmaior,

can any way to return value from the store proceduce?
rather than after that query re-select the data.

if my output is array? the store proceduce can support it?

from

sonny
0
virmaiorCommented:
By definition, a procedure does not return a value.  Only a function does.

As such, you should use a function if you plan on returning a value.  To return a set of values, you could either GROUP_CONCAT them at the end of the function or else query through them in a temporary table.

What you are wanting actually seems best-suited to a VIEW
since you want to be able to query all of the admin users at once.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.