Re: In Stored Procedure, how to check if variable is NULL?

[Roland Bouman]

> In MYSQL I have so far:
>
> /*GetUser*/
> CREATE PROCEDURE `eiffelsupport`.`GetUser`
> (
> a_Username VARCHAR(50)
> )
> BEGIN
> DECLARE l_UserID INT;
>
> SELECT l_UserID = UserID FROM Memberships WHERE
> Username = a_Username;
> IF l_UserID IS NULL
> SELECT FirstName, LastName, EMail FROM Users
> WHERE UserID = l_UserID;
> END


What's wrong superficially is this line:

SELECT l_UserID = UserID FROM Memberships WHERE

you are using the comparison operator

=

instead of the assignment operator

:=

But just on the side this seems very complicated code for something very simple. Why not do:

CREATE PROCEDURE `eiffelsupport`.`GetUser` (
a_Username VARCHAR(50)
)
BEGIN
SELECT FirstName
, LastName
, EMail
FROM Memberships m
INNER JOIN Users u
ON m.UserID = u.UserID
WHERE m.Username = a_Username;
END;

Another tip, if you really do need to initialize variables from a single row select, use the SELECT ...INTO syntax. It's much more explicit, and immediately show a programmer what's going on.

Good luck!