Simple MySQL function error

Question

I'm trying to write a MySQL function which returns whether an username is in my table or not. It's the following:

CREATE FUNCTION UserExists (pUserName VARCHAR(40))
RETURNS BIT DETERMINISTIC

BEGIN

DECLARE rVal BIT;

IF EXISTS (SELECT * FROM Users WHERE userName = pUserName)
THEN SET rVal = 1;
ELSE SET rVal = 0;
END IF;

RETURN rVal;

END;

However, I get an error. Any ideas?

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 6. — Sait
– Sait, Commented Feb 2, 2013 at 17:11

echo_Me · Accepted Answer · 2013-02-02 17:15:37Z

2

try this

    IF(EXISTS(SELECT * FROM Users WHERE userName = pUserName))

answered Feb 2, 2013 at 17:15

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

John Woo · Accepted Answer · 2013-02-02 17:12:09Z

1

how about using user define variable?

DELIMITER $$

CREATE FUNCTION UserExists (pUserName VARCHAR(40))
RETURNS BIT DETERMINISTIC
BEGIN

    DECLARE rVal BIT;
    SET @recCount := (SELECT COUNT(*) FROM Users WHERE userName = pUserName);
    IF @recCount > 0 THEN 
        SET rVal = 1;
    ELSE 
        SET rVal = 0;
    END IF;
    RETURN rVal;

END $$

DELIMITER ;

answered Feb 2, 2013 at 17:12

John Woo

265k70 gold badges509 silver badges500 bronze badges

3 Comments

Sait Over a year ago

Thanks for your reply. However, didn't work either. Error: #1419 - You do not have the SUPER privilege and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)

John Woo Over a year ago

the error is not the syntax but because maybe you don't have enough privilege to create a function.

Sait Over a year ago

I have a shared godaddy hosting account. Any idea to solve this issue?

thimages · Accepted Answer · 2013-02-03 03:20:18Z

0

Before you query your mysql database, you need to use %mysql sudo -u -p and then give your commands.

answered Feb 3, 2013 at 3:20

thimages

112 bronze badges

Collectives™ on Stack Overflow

Simple MySQL function error

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related