SELECT ... INTO在存储过程中返回null

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 when the query:

即使查询时始终返回null:

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

returns a valid value for the same value used in and the function parameter.

返回与函数参数中使用的相同值的有效值。

For instance:
SELECT id FROM cards WHERE number=12345 AND ENABLED = 1 LIMIT 1;
-- returns an id, while
GetCardId(12345);
-- returns null

例如:SELECT id FROM cards WHERE number = 12345 AND ENABLED = 1 LIMIT 1; - 返回一个id,而GetCardId(12345); - 返回null

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

我在这里缺少什么想法?我认为自己对SQL非常熟练,但在SP上有点绿色。

3 个解决方案

#1

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

您正在使用的功能有多大?这个数字是否可能大于适合INT的数字?

#2

Christopher here is your function. 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

#3

Always returns NULL:

始终返回NULL:

Get rid of DETERMINISTIC clause in Procedure definition. MySQL caches the responses from such procedure or functions.

删除过程定义中的DETERMINISTIC子句。 MySQL缓存来自这些过程或函数的响应。

Excerpt from MySQL:

摘自MySQL:

A routine is considered “deterministic” if it always produces the same result for the same input parameters, and “not deterministic” otherwise. If neither DETERMINISTIC nor NOT DETERMINISTIC is given in the routine definition, the default is NOT DETERMINISTIC. To declare that a function is deterministic, you must specify DETERMINISTIC explicitly

如果例程对于相同的输入参数总是产生相同的结果,则该例程被认为是“确定性的”,否则被认为是“不确定的”。如果在例程定义中既未给出DETERMINISTIC也未给出DETERMINISTIC,则默认值为NOT DETERMINISTIC。要声明函数是确定性的,必须明确指定DETERMINISTIC

MySQL 5.5 - Creating Procedure or Function

MySQL 5.5 - 创建过程或函数

#1