MySQL Forums
Forum List  »  Stored Procedures

New to sp, ERROR 1172 (42000): Result consisted of more than one row
Posted by: Alicia
Date: December 01, 2005 05:15AM

Hello,

I am new to stored procedures and I am trying to do a checking of my db data. But I get an error:

Existing tables:

mysql> describe pet;//
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| owner | varchar(20) | YES | | NULL | |
| species | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birth | date | YES | | NULL | |
| death | date | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.02 sec)

mysql> describe event;//
+--------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| date | date | YES | | NULL | |
| type | varchar(15) | YES | | NULL | |
| remark | varchar(255) | YES | | NULL | |
| owner | varchar(20) | YES | | NULL | |
+--------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

Stored procedure:

delimiter //
create procedure test.check_owner
(out name_id varchar(10), out species_id varchar(10))
language sql
not deterministic
sql security invoker
comment 'a procedure to check owner between event and pet tables'
BEGIN
declare comp varchar(15);
declare name_id2 varchar(10);
declare species_id2 varchar(10);
SELECT pet.owner<>event.owner,event.name,pet.species INTO comp,name_id2,species_id2 FROM event,pet WHERE pet.name=event.name;
IF comp=1 THEN
SET name_id=name_id2;
SET species_id=species_id2;
END IF;
END;//

I have tried with CASE too, instead of IF-THEN-ENDIF:

CASE comp
WHEN 1 THEN SET name_id=name_id2;
SET species_id=species_id2;
END CASE;
END;//

When I call it, I get this error:

mysql> CALL test.check_owner (@name_id,@species_id);//
ERROR 1172 (42000): Result consisted of more than one row

I want to obtain all rows that owner is different in both tables for the same record in both tables. I want to have the name and the specie of that different rows.

Thanks for your help,
Alicia

Options: ReplyQuote


Subject
Views
Written By
Posted
New to sp, ERROR 1172 (42000): Result consisted of more than one row
29304
December 01, 2005 05:15AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.