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.
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?
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
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