Stored Procedure

[dgillz]

I am attempting to create a stored procedure in Pervasive Control Center 2000i and I keep getting an syntax error:

[Pervasive][ODBC Client Interface][LNA][Pervasive][ODBC Engine Interface][Data Record Manager][SPEng]1: 'AS': Syntax error

The following is my code:

CREATE PROCEDURE modscrn AS

select screen_id, screen_name from adscrfil_sql where (screen_name <> 'MACOLA') union
select screen_id, screen_name from apscrfil_sql where (screen_name <> 'MACOLA')

Any ideas what the syntax error is?

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[TomKane]

I think it might be the word 'AS' - I have P.SQL 7 but the syntax to create a stored procedure is as follows:

CREATE PROCEDURE <procedure name> <parameter list> ';'
<SELECT statement>

 

[mirtheil]

Actually, you're going to need to do something like this:
CREATE PROCEDURE DATERETURNPROC(IN DATE 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 = DATE;
END;

or in your case:
CREATE PROCEDURE modscrn()
returns (
screen_id <datatype>, screen_name <datatype>);
begin
select screen_id, screen_name from adscrfil_sql where (screen_name <> 'MACOLA') union
select screen_id, screen_name from apscrfil_sql where (screen_name <> 'MACOLA');
end;

For more information, look at the SQL Engine Reference for Create Procedure.


info@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician

 

[dgillz]

What am I supposed to put in place of <datatype> ? Both fields are text fields, but pervasive should already know this.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[mirtheil]

For <datatype>, you need to put the actual data type. If it's a char(10), then that's what you need.

info@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician