Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2009
    Posts
    14

    Unanswered: Returns Clause on Stored Proc

    I want to return a dataset (records) from a stored proc. The documentation is extremely vague on the RETURNS clause.

    Can someone please explain it in English.

    Thanks,
    Bill

  2. #2
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    Short answer is that you need to specify the field names and data types in the returns clause.
    An easier way would be to give a simple example:
    Code:
    CREATE PROCEDURE DATERETURNPROC(IN :PDATE DATE)
    RETURNS(
    ID INTEGER,
    Name CHAR(7),
    Section CHAR(3),
    Max_Size USMALLINT,
    Start_Date DATE,
    Start_Time TIME,
    Finish_Time TIME,
    Building_Name CHAR(25),
    Room_Number UINTEGER,
    Faculty_ID UBIGINT
    );
    BEGIN
    SELECT ID, Name, Section, Max_Size, Start_Date, Start_Time, Finish_Time, Building_Name, Room_Number, Faculty_ID FROM CLASS WHERE START_DATE = :PDATE;
    END;
     
    CALL DATERETURNPROC('1995-06-05')
    This example uses DEMODATA. As you can see, the RETURNS clause is the same as the fields in the SELECT list.

    If you're having specific issues, post the CREATE PROCEDURE and the behavior you are seeing.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  3. #3
    Join Date
    Jan 2009
    Posts
    14
    Thanks, but MSSQL sure seems easier.

    Bill

  4. #4
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    Personally, I don't use Stored Procedures for any database. I find that it causes more problems than it solves. Most of the problems I've seen is that developers want the stored procedure to do more than the database was designed for. I've seen Stored Procedures that take up several pages but could be solved with 10 lines of application code.
    What are you trying to do? Perhaps there's a better way in PSQL.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  5. #5
    Join Date
    Jan 2009
    Posts
    14
    Here is the proc:

    CREATE PROCEDURE "GetPrevLandings"(
    in :TDate DATE, in :TTime INTEGER, in :AID CHAR(4))
    RETURNS(
    "Trip Number" INTEGER,
    "Leg Number" INTEGER,
    "Arrival Date - Local" INTEGER,
    "TimeVal" DOUBLE,
    "ArrDate" DATE
    );
    BEGIN
    Select TOP 1 "Trip Number",
    "Leg Number",
    "Arrival Date - Local",
    (("ETA - Local" * 1.0) / 60 ) as TimeVal,
    Max(DateAdd(day, "Arrival Date - Local", 0000-01-00)) As ArrDate
    from "Trip Legs"
    Where "Arrival Date - Local" <= DateDiff(day, '1899-12-30' , :TDate)
    AND
    "ETA - Local" < :TTime
    AND
    "Aircraft ID" = :AID
    AND
    "Trip Number" < 9000000
    Group By "Arrival Date - Local",
    "ETA - Local",
    "Trip Number",
    "Leg Number"
    Order by "Arrival Date - Local" DESC,
    "Trip Number" DESC,
    "Leg Number" DESC;
    END

    here is the Call:

    CALL GetPrevLandings('2009-05-22', 996, '96CH');

    The Proc is getting created but the call gives an error: 'Invalid Argument Value'

    I believe it is the lat one - the Char.

    I am trying to learn how to use a SP in pervasive. This is designed to pull the most recent landing of an aircraft. The SQL works as expected but the call to the proc is a problem.

    Thanks.

    Bill

  6. #6
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    Actually, I think it might be this line:
    Code:
      Max(DateAdd(day, "Arrival Date - Local", 0000-01-00)) As ArrDate
    It should be:
    Code:
      Max(DateAdd(day, "Arrival Date - Local", '0000-01-01')) As ArrDate
    First, 0000-01-00 isn't a valid date and second it needs to be enclosed in single quotes.
    Also, if the SQL is working outside the Stored Procedure, you usually won't get much (if any) performance benefit by using Stored Procedures.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •