?
Solved

Mysql Stored Procedure cant return value

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

719 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