Re: In Stored Procedure, how to check if variable is NULL?
> 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!
Subject
Written By
Posted
Re: In Stored Procedure, how to check if variable is NULL?
August 22, 2005 03:06AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.