Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm trying to get my head around stored procedures for use in a web application. It seems to me that whatever the last statement is in a MySQL stored procedure is seems to be what gets treated as that procedure's result set. Unfortunately though, it seems there are references in the MySQL docs that say a procedure can return multiple result sets. How is this behavior triggered? How can I tell the MySQL server that I explicitly want only one result set returned?

(For instance, I have a query that does a SELECT and a few inserts. I don't want to tell clients about the inserts, but I do want the client to be given the SELECT's result set....)

share|improve this question
add comment

1 Answer

Each SELECT statement that does not insert into a table or a variable will produce a result set.

If you want your stored procedure to return only one result set, make sure that you only have one SELECT statement. If you have other SELECT statements, make sure that they insert results into a table or variable.

UPDATE
Here are examples of stored procedures.

This stored procedure would return one result set:

DELIMITER ;;
CREATE DEFINER=CURRENT_USER PROCEDURE stored_procedure_name()
BEGIN
    DECLARE local_variable_name INT;

    SELECT column_name FROM table_1 LIMIT 1 INTO local_variable_name;

    SELECT * FROM table_1;
END;;
DELIMITER ;

This stored procedure would return two result sets:

DELIMITER ;;
CREATE DEFINER=CURRENT_USER PROCEDURE stored_procedure_name()
BEGIN
    DECLARE local_variable_name INT;

    SELECT column_name FROM table_1 LIMIT 1 INTO local_variable_name;

    SELECT * FROM table_1;

    SELECT * FROM table_2;
END;;
DELIMITER ;
share|improve this answer
    
Ah, so if it goes into a table or variable, it doesn't get included as a result? How does one access that; using e.g. SELECT INTO? –  Billy ONeal Nov 23 '11 at 20:20
    
Yes to first question. I'm not clear on your second question. If I want to save a value into a local variable from within the stored procedure, I run something like this: SELECT column_name LIMIT 1 INTO local_variable_name;. –  dabest1 Nov 23 '11 at 21:43
    
Sample query above should have been: SELECT column_name FROM table LIMIT 1 INTO local_variable_name;. –  dabest1 Nov 23 '11 at 21:51
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.