Solved

Mysql Stored Procedure cant return value

Posted on 2008-06-20
5
2,283 Views
Last Modified: 2013-12-12
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?

0
Comment
Question by:sonny_j81
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 20

Expert Comment

by:virmaior
ID: 21831033
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
 
LVL 1

Author Comment

by:sonny_j81
ID: 21836185
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
 
LVL 20

Expert Comment

by:virmaior
ID: 21836235
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
 
LVL 1

Author Comment

by:sonny_j81
ID: 21836368
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
 
LVL 20

Accepted Solution

by:
virmaior earned 500 total points
ID: 21837450
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

Featured Post

Don't Cry: How Liquid Web is Ensuring Security

WannaCry is just the start. Read how Liquid Web is protecting itself and its customers against new threats.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question