Returns Clause on Stored Proc

Bill Slaughter · #1 (**permalink**) 05-05-09, 10:49

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

mirtheil · #2 (**permalink**) 05-05-09, 11:04

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.

Bill Slaughter · #3 (**permalink**) 05-05-09, 12:27

Thanks, but MSSQL sure seems easier.

Bill

mirtheil · #4 (**permalink**) 05-05-09, 12:46

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.

Bill Slaughter · #5 (**permalink**) 05-05-09, 16:57

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

mirtheil · #6 (**permalink**) 05-05-09, 17:53

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech