community.borland.com

Article #25500: Multiple Rows in Singleton Select error in Stored Procedure

Problem:
I am getting the error "Multiple rows in singleton select" when I run my stored procedure.  What 
could cause this error?

Solution:
Note: The information was tested under InterBase 5.0 and 5.5.



This error occurs when you try to run a select statement in a situation where the procedure only
expects to see one value.


eg.  if your stored procedure reads:

SET TERM ^;
	CREATE PROCEDURE SP_JUNK (WhichOrder char(1))
		RETURNS (return1 CHAR(1), return2 char(1)) AS 
	BEGIN
		/* find the correct order and assign variables */
		SELECT a, b
			FROM Dudley
			WHERE a = :WhichOrder
			INTO :return1, :return2;
		EXIT;
	END ^
	SET TERM ;^



And the table dudley has the following values

A        B 
---     ---
a        g
a        c
d        f

If you call the stored procedure with :

execute procedure sp_junk('a')

you will get this error : Statement failed, SQLCODE = -811   
                                        multiple rows in singleton select

This happens because the stored procedure expects that your result set will 
have only one row.   In this case, the result set is two records.  This triggers the
error.  If the user called the procedure with
 
execute procedure sp_junk('d')

they would not get the error, since only one row would be returned.

Last Modified: 26-OCT-00