CODEWITHSUNDEEP
mysqlstored-procedures
Carmelo Ricarte Rocamora
Carmelo Ricarte Rocamora

Reputation: 13

error when I call procedure in mysql:Error Code: 1222. The used SELECT statements have a different number of columns

delimiter /
drop procedure if exists piez_vend;/
create procedure piez_vend (IN _piez varchar(20), out nvend int(11))
begin
SELECT numpieza, count(numvend) into nvend from preciosum where numpieza like _piez;
end
/
delimiter ;

call piez_vend('dd-0001-210',@nvend);

Sample data

 numpieza, numvend, preciounit, diassum, descuento
'a-1001-l', '1', '1.60', '3', '0'
'a-1001-l', '3', '3.00', '1', '0'
'c-400-z', '1', '7.80', '4', '5'
'c-400-z', '6', '6.50', '3', '0'
'dd-0001-210', '1', '300.00', '3', '15'
'dd-0001-210', '2', '310.00', '5', '12'
'dd-0001-210', '4', '287.00', '15', '10'
'm-0001-c', '1', '550.00', '3', '10'
'm-0001-c', '5', '570.00', '7', '15'
't-0002-at', '2', '25.80', '3', '0'
't-0002-at', '4', '27.00', '5', '7'

Upvotes: 1

Views: 753

Answers (1)

O. Jones
O. Jones

Reputation: 108736

The error message is a little obtuse.

The used SELECT statements have a different number of columns

It means this query

SELECT numpieza, count(numvend) into nvend from preci...

doesn't work because the SELECT mentions two columns. But you're using into to tell MySQL where to put the value of only one of the columns. It doesn't know what to do with the other column. Try this instead.

SELECT count(numvend) into nvend from preci...

Pro tip Stored procedures are tricky to debug. It's helpful to try the queries in them before you wrap them in the procedure.

Upvotes: 3

Related Questions