Help w/ this Interbase Stored Procedures 
Author Message
 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



Wed, 18 Jun 1902 08:00:00 GMT  
 Help w/ this Interbase Stored Procedures

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

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




Wed, 18 Jun 1902 08:00:00 GMT  
 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




Wed, 18 Jun 1902 08:00:00 GMT  
 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



Wed, 18 Jun 1902 08:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Live resultsets from Interbase stored procedure?

2. BLOB FROM DELPHI TO INTERBASE STORED PROCEDURES

3. Interbase with views or stored procedures.

4. Creating Interbase Stored Procedure

5. Local Interbase Server - Stored Procedures ?

6. Stored procedure question - Interbase NLM.

7. Interbase - stored procedures

8. Interbase with views or stored procedures.

9. Dynamic WHERE clause in Stored Procedures within INTERBASE ?

10. Granting Interbase privledges from within a stored procedure

11. Interbase Stored Procedures

12. Problem with Interbase Stored Procedure

 

 
Powered by phpBB® Forum Software