At 06:34 AM 28/02/2005 +0000, you wrote:

>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.

Firstly, the query is an inner join that finds no match between the two
tables - hence the DSQL query returns no rows.

Inside the SP. the query also returns no data to the variables - hence the
(uninitialised) variables simply return their unchanged values (nulls) as
return values to the SP call.

./heLen