Hi,

I have a stored procedure and a query that do the same thing. They
both handle login information. However if there is no match for the
user name or password the query returns 0 records, but the stored
procedure returns 1 record with null values in each field. I want to
test if the query didn't return any records, but with the stored
procedure I always get a record returned even if there are no matches.
Is this how stored procedures work in Firebird? I am using v 1.5.2,
the latest version. I am trying to use stored procedures as much as
possible, so this is important to me. The query is:

SELECT "USER".USERID, BKDN.USERLEVEL
FROM BKDN, "USER"
WHERE BKDN.USERNAME ='test'
AND BKDN.PW ='test'
AND "USER".USERID = BKDN.USERID
AND BKDN.USERACTIVE = 'Y'

The stored procedure is:

SET TERM ^ ;

CREATE PROCEDURE PROC_LOGIN (
UNAME VARCHAR(20),
PW VARCHAR(15))
RETURNS (
UID INTEGER,
ULEVEL INTEGER)
AS
BEGIN
SELECT "USER".USERID, BKDN.USERLEVEL
FROM BKDN, "USER"
WHERE BKDN.USERNAME =:UNAME
AND BKDN.PW =:PW
AND "USER".USERID = BKDN.USERID
AND BKDN.USERACTIVE = 'Y'
INTO :UID, :ULEVEL;
SUSPEND;
END
^

SET TERM ; ^

GRANT SELECT ON BKDN TO PROCEDURE PROC_LOGIN;

GRANT SELECT ON "USER" TO PROCEDURE PROC_LOGIN;

GRANT EXECUTE ON PROCEDURE PROC_LOGIN TO SYSDBA;

Why the discrepency in returning 0 records vs 1 record with null
values in the stored procedure. Thanks for any input.

Regards,
Roy F.