CODEWITHSUNDEEP
mysql
Michele Schiavo
Michele Schiavo

Reputation: 11

Don't understand why

I have this Stored Function that is supposed to return something from my database

CREATE DEFINER=`michele`@`%` FUNCTION `ProssimaDomanda`(`ParamIDQuestionario` INT(20), `ParamSessioneID` VARCHAR(20), `ParamUltimaDomanda` BIGINT(20)) RETURNS bigint(20) unsigned
    READS SQL DATA
BEGIN
    DECLARE Ritorno BIGINT(20) UNSIGNED DEFAULT 0;

    SET Ritorno=(SELECT NextIDDomanda 
        FROM Risposte
        INNER JOIN OpzioniDomande ON Risposte.IDRisposta = OpzioniDomande.IDRisposta
        WHERE 
            Risposte.SessioneID='ParamSessioneID'
                AND
            Risposte.IDQuestionario=ParamIDQuestionario
                AND
            Risposte.IDDomanda=ParamUltimaDomanda);

    /*IF (Ritorno IS NULL) 
        THEN SET Ritorno=(SELECT PrimaDomanda FROM PrimeDomande WHERE IDQuestionario=ParamIDQuestionario);
    END IF;*/


RETURN Ritorno;

END

But when I call it:

SELECT ProssimaDomanda(1,1554891825,2);

It returns Null.

Doing a normal select:

SELECT NextIDDomanda
        FROM Risposte
        INNER JOIN OpzioniDomande ON Risposte.IDRisposta = OpzioniDomande.IDRisposta
        WHERE 
            Risposte.SessioneID='1554891825'
                AND
            Risposte.IDQuestionario=1
                AND
            Risposte.IDDomanda=2;

Returns 3 which is correct.

Why does this happen?

Upvotes: 0

Views: 43

Answers (2)

Robert Kock
Robert Kock

Reputation: 6018

Within your function, in the where-clause, you put ParamSessioneID within quotes being therefore interpreted as a literal string and not the parameter value.
Change your function into:

CREATE DEFINER=`michele`@`%` FUNCTION `ProssimaDomanda`
  (`ParamIDQuestionario` INT(20),
   `ParamSessioneID` VARCHAR(20),
   `ParamUltimaDomanda` BIGINT(20)
  )
RETURNS bigint(20) unsigned
    READS SQL DATA
BEGIN
    DECLARE Ritorno BIGINT(20) UNSIGNED DEFAULT 0;

    SET Ritorno=(SELECT NextIDDomanda 
        FROM Risposte
        INNER JOIN OpzioniDomande ON Risposte.IDRisposta = OpzioniDomande.IDRisposta
        WHERE 
            Risposte.SessioneID=ParamSessioneID -- This is where you have your problem!!!!
                AND
            Risposte.IDQuestionario=ParamIDQuestionario
                AND
            Risposte.IDDomanda=ParamUltimaDomanda);

    /*IF (Ritorno IS NULL) 
        THEN SET Ritorno=(SELECT PrimaDomanda FROM PrimeDomande WHERE IDQuestionario=ParamIDQuestionario);
    END IF;*/


RETURN Ritorno;

END

Upvotes: 1

Arun Kumar
Arun Kumar

Reputation: 121

Actually INNER JOIN clause matches rows in one table with rows in other tables and allows you to query rows that contain columns from both tables.

Upvotes: 0

Related Questions