Oracle Procedure and ADO

From: Phill Atkinson <Phill_at_tibble.net>
Date: Mon, 29 Jan 2001 11:38:13 GMT
Message-ID: <FCcd6.57497$pp2.3961518_at_news3.cableinet.net>

Can anyone help?

I'm trying to return a recordset to ADO from an Oracle procedure.

In MS SQL the following would work ok:

create procedure spSelStaff
_at_nStaffID Int =null
as

begin

 if _at_nStaffID is null
  begin
   select s.StaffID,

    s.TitleID,
    t.Title,
    s.FirstName,
    s.LastName

   from Staff s
   inner join Title t
   on s.TitleID = t.TitleID
  end
 else
  begin
   select s.StaffID,

    s.TitleID,
    t.Title,
    s.FirstName,
    s.LastName

   from Staff s
   inner join Title t
   on s.TitleID = t.TitleID
   where StaffID = _at_nStaffID
  end

end

In Oracle this is invalid. Why?

create or replace procedure scott.test is begin

  select *
    from emp;

end; Received on Mon Jan 29 2001 - 12:38:13 CET