Clever Geek Handbook

SELECT ... INTO returns null in stored procedure

This function:

CREATE FUNCTION `GetCardID`(numId INT) RETURNS int(11)
    DETERMINISTIC
BEGIN
    DECLARE retcard INT(11);
    SELECT id
    INTO retcard
    FROM cards
    WHERE `number` = numId
        AND enabled = 1
    LIMIT 1;
    RETURN retcard;
END

Always returns null, even if the request is:

SELECT id FROM cards WHERE `number`=<Insert Value Here> AND ENABLED = 1 LIMIT 1;

returns a valid value for the same value as in the function parameter.

For example:
SELECT id FROM cards WHERE number = 12345 AND ENABLED = 1 LIMIT 1,
- returns the identifier, and GetCardId (12345);
- returns null

Any ideas what I'm missing here? I consider myself quite proficient in SQL, but a little green on SP.

+2


source to share


3 answers


How big is the data you are using in your function? Is it possible that the number is larger than what will fit in an INT?



+1


source


Christopher is your function here. Try this and it should work:



CREATE FUNCTION [dbo].[GetCardID]
(  
    @Num_ID INT

)  
RETURNS int  
AS  
BEGIN  
    declare @retcard int    

    select Top 1 @retcard = id 
    FROM cards 
    where number = @num_Id
    AND enabled = 1

    return @retcard



END
+1


source


Always returns NULL

:

Get rid of the proposal DETERMINISTIC

in the definition of the procedure. MySQL caches responses from such a procedure or functions.

Excerpt from MySQL:

A subroutine is considered "deterministic" if it always produces the same result for the same inputs, and "non-deterministic" otherwise. If neither DETERMINISTIC nor NOT DETERMINISTIC is specified in the standard definition, the default is NOT DETERMINISTIC. To declare that a function is deterministic, you must explicitly specify DETERMINISTIC

MySQL 5.5 - Creating a Procedure or Function

0


source






More articles:


All Articles