Function inside the sql server

Asked

Viewed 30 times

0

I’m trying to make a function inside the sql server, however, I can not use it because of an error saying that the result consists of more than one line (Error Code: 1172 Result consisted of more than one Row) I wonder how I can solve this

`CREATE FUNCTION `retornaSolo`(id int(11)) RETURNS varchar(80) CHARSET latin1
BEGIN
declare solos varchar (80);
select tipo_solo into solos from solo where status_solo = 'Ativado' 
and cod_usuario = id;
RETURN solos;
END`
mysql sql

  • what do you want to return with the function ? just one data ? several data ? a table ?

  • http://tsqlmaster.blogspot.com.br/2013/05/criando-umaprocedure-com-cursor-no.html .... This"into" allows the return of only one value , your select "activated" should return more than one line , read on cursors.

  • i intend to return various data, which in case would be all types of soils registered by the user

1 answer

0

Function returns only one result, what you can do is create a stored Procedure, or bring the results concatenated and separated by "," which would look something like this:

DROP FUNCTION IF EXISTS retornaSolo;
CREATE FUNCTION `retornaSolo`(id int(11)) 
RETURNS varchar(80) CHARSET latin1
BEGIN

    DECLARE solos varchar (80);

    SELECT 
            GROUP_CONCAT(DISTINCT tipo_solo SEPARATOR ',') 
    INTO solos 
    FROM solo 
    WHERE status_solo = 'Ativado' 
    AND cod_usuario = id;

RETURN solos;
END;

or a stored Procedure that would be so:

DROP PROCEDURE IF EXISTS sp_retornaSolo;
DELIMITER |
CREATE PROCEDURE sp_retornaSolo(id INT(11))
BEGIN

    SELECT
        tipo_solo
    FROM solo
    WHERE status_solo = 'Ativado'
    AND cod_usuario = id;


END
|
DELIMITER ;

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.