Hi Helen,

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

Thanks for that explanation. It makes sense now. I tried changing the
SP like Dimitry recommended but it still returns the null variables.
So the question now is, is there anyway for the SP to not return
anything if the query inside doesn't? Would it be better to just check
to see if the value of the variable from the SP is null rather than
checking if it returned a record?

I'm also a little unclear on some aspects of Firebird SP's. How do you
know when to use the FOR....DO as Dimitry suggested, what about
SUSPEND vs EXIT, etc? Is there any good detailed documentation about
SP's in Firebird? I am very impressed with Firebird so far, I just
need to learn it's nuances a bit better. Thanks so much for your help
with this. I appreciate it a lot.

Regards,
Roy F.


--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
wrote:

> 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 "USE

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