Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Mysql Stored Procedure cant return value

Posted on 2008-06-20
5
Medium Priority
?
2,288 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
  • 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 1500 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
The viewer will learn how to count occurrences of each item in an array.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

885 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