MySQL过程始终返回True [英] MySQL Procedure Always Returns True

问题描述

有人可以告诉我为什么这总是会返回true，无论计数是否为0？这让我疯了！

Can someone PLEASE tell my why this ALWAYS returns true, no matter if the count is 0 or not? This is driving me insane!

delimiter //
create procedure UserRole_IsUserAdmin(userID int)
begin
    if userID is null then
        select 'User ID is required.';
    else
        if (select count(UserRoleCode) from UserRole where RoleCode = 1 and UserID = userID and NPOCode is null and VendorCode is null) > 0 then
            select true;
        else
            select false;
        end if;
    end if;
end;
//

有时查询返回0，但if仍然计算为true。我不知道为什么...

The query returns 0 sometimes, yet the if still evaluates to true. I'm not sure why...

推荐答案

请代之以你的代码，试试吧：

Instead your code, please, try it:

CREATE PROCEDURE UserRole_IsUserAdmin(userID int)
BEGIN
    IF (userID IS NULL) THEN
       SELECT 'User ID is required.';
    ELSE
       SELECT CASE WHEN UserRoleCode IS NULL THEN 'FALSE' ELSE 'TRUE' END AS Result
       FROM UserRole
       WHERE RoleCode = 1 and UserID = userID;
    END IF;
END;

在上面的例子中CASE运算符 [ ^ ]已被使用。



但最简单的方法是用默认值（例如零）替换 userID 变量，只返回 true 或 false ;）



In above example CASE operator[^] has been used.

But the simplest way is to replace userID variable with default value (for example zero) to return only true or false as a result ;)

CREATE PROCEDURE UserRole_IsUserAdmin(userID int)
BEGIN
       SELECT CASE WHEN UserRoleCode IS NULL THEN 'FALSE' ELSE 'TRUE' END AS Result
       FROM UserRole
       WHERE RoleCode = 1 and UserID = COALESCE(userID,0);
END;

更多信息：COALESCE功能 [ ^ ]





尝试使用用户定义的变量 [ ^ ] ：

Further information: COALESCE function[^]


Try to use User-defined variable[^]:

CREATE PROCEDURE UserRole_IsUserAdmin(userID int)
BEGIN
       DECLARE urc VARCHAR(30);

       SELECT UserRoleCode INTO urc
       FROM UserRole
       WHERE RoleCode = 1 and UserID = COALESCE(userID,0);

       SELECT IF(urc IS NULL,false,true);

END;

[/ EDIT]

[/EDIT]

这篇关于MySQL过程始终返回True的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！