Board index » delphi » Help w/ this Interbase Stored Procedures

Help w/ this Interbase Stored Procedures

I have a Stored Procedure that's not working like I'd expect. Basically, I
want to
  1. select a row from my employee table where name=(the name I pass)
  2. If no row with that name exists (meaning there's no employee by that
name) I want to return an employee where name="Guest".  This entry exists
in my Employee table but my procedure is still not working
correctly....Why?

CREATE PROCEDURE EMPLOYEE_DETAILS_BYUSERNAME (
  USERNAME CHAR(30)
) RETURNS (
  ID INTEGER,
  FIRSTNAME VARCHAR(50),
  MIDDLENAME VARCHAR(30),
  LASTNAME VARCHAR(50),
  SSN CHAR(9),
  NTUSERNAME VARCHAR(30)
) AS        
BEGIN
   FOR SELECT EMPLOYEEID, FIRSTNAME, MIDDLENAME, LASTNAME, SSN, NTUSERNAME
   FROM EMPLOYEE
   WHERE NTUSERNAME=:UserName
   INTO :ID, :FirstName, :MiddleName, :LastName, :SSN, :NTUserName

   DO
     BEGIN
       IF (LastName IS NULL) THEN
       BEGIN
         SELECT EMPLOYEEID, FIRSTNAME, MIDDLENAME, LASTNAME, SSN,
NTUSERNAME
         FROM EMPLOYEE
         WHERE NTUSERNAME="Guest"
         INTO :ID, :FirstName, :MiddleName, :LastName, :SSN, :NTUserName;
       END
       SUSPEND;
     END
END

 

Re:Help w/ this Interbase Stored Procedures


On 2 Dec 97 17:11:49 GMT, "Randy W. Trexler"

Quote
<randy.trex...@med.va.gov> wrote:
>I have a Stored Procedure that's not working like I'd expect. Basically, I
>want to
>  1. select a row from my employee table where name=(the name I pass)
>  2. If no row with that name exists (meaning there's no employee by that
>name) I want to return an employee where name="Guest".  This entry exists
>in my Employee table but my procedure is still not working
>correctly....Why?

>       IF (LastName IS NULL) THEN

I can imagine that this line is unreached if entry wasn't found.
Perhaps Interbase causes an exception when yuo try to reference a
field on EOF. Maybe it will help to use a WHEN...DO statement to
capture the Exception.

I have only read the Interbase books and have no experience with
stored procedure programming but perhaps this is the reason.

Hope this helps

Holger

Holger.W...@Bigfoot.com>

Re:Help w/ this Interbase Stored Procedures


Um... I had a closer look to your proc....

if you use a FOR SELECT .... DO construct and no records where found,
the DO block won't be executed. It's like a WHILE NOT EOF DO... in
pascal.

Holger

Holger.W...@Bigfoot.com

Re:Help w/ this Interbase Stored Procedures


Randy W. Trexler a crit:

Quote
> I have a Stored Procedure that's not working like I'd expect. Basically, I
> want to
>   1. select a row from my employee table where name=(the name I pass)
>   2. If no row with that name exists (meaning there's no employee by that
> name) I want to return an employee where name="Guest".  This entry exists
> in my Employee table but my procedure is still not working
> correctly....Why?

if there is only one ntusername corresponding to USERNAME, you can write:

CREATE PROCEDURE EMPLOYEE_DETAILS_BYUSERNAME (
  USERNAME CHAR(30)
) RETURNS (
  ID INTEGER,
  FIRSTNAME VARCHAR(50),
  MIDDLENAME VARCHAR(30),
  LASTNAME VARCHAR(50),
  SSN CHAR(9),
  NTUSERNAME VARCHAR(30)
) AS
BEGIN
  /* You look for
   SELECT EMPLOYEEID, FIRSTNAME, MIDDLENAME, LASTNAME, SSN, NTUSERNAME
   FROM EMPLOYEE
   WHERE NTUSERNAME=:UserName
   INTO :ID, :FirstName, :MiddleName, :LastName, :SSN, :NTUserName

    IF (LastName IS NULL) THEN
       BEGIN
         SELECT EMPLOYEEID, FIRSTNAME, MIDDLENAME, LASTNAME, SSN,
NTUSERNAME
         FROM EMPLOYEE
         WHERE NTUSERNAME="Guest"
         INTO :ID, :FirstName, :MiddleName, :LastName, :SSN, :NTUserName;
       END
     END
     /* it's usefull only if you want to write 'SELECT' clause */
     SUSPEND;
END

Other Threads