thanks for the reply...but this doesn't compile! (RC5)

I added as "AS" which I think I need, but I still get the following error
on the code below:

CREATE PROCEDURE MY_PROC(CAT_ID INTEGER)
RETURNS (ID INTEGER, TITLE VARCHAR(100),EXP_DATETIME TIMESTAMP)
AS
BEGIN
FOR SELECT id, title, ExpirationDateTime
FROM forms INNER JOIN formcategory
ON forms.id=formcategory.form_id
WHERE category_id=:CAT_ID
ORDER BY UpperTitle
INTO :ID, :TITLE, :EXP_DATETIME;
DO BEGIN
SUSPEND;
END
END
______________________________
Dynamic SQL Error
SQL Error Code = -104
Token Unknown - line 10 char 15
?.
______________________________

Any ideas????

I am creating this through InterBase PlanAnalyzer - I don't need to set TERM right?

Many thanks,

David






"jjochoa75" <jochoa@...> on 21/10/2003 06:17:22 PM

Please respond to firebird-support@yahoogroups.com

To: firebird-support@yahoogroups.com
cc:
Subject: [firebird-support] Re: Stored Procedures?


Try this:

CREATE PROCEDURE MY_PROC(CAT_ID INTEGER)
RETURNS (ID INTEGER, TITLE VARCHAR(100),EXP_DATETIME TIMESTAMP)
DO BEGIN
FOR SELECT id, title, ExpirationDateTime
FROM forms INNER JOIN formcategory
ON forms.id=formcategory.form_id
WHERE category_id=:CAT_ID
ORDER BY UpperTitle
INTO :ID, :TITLE, :EXP_DATETIME
DO BEGIN
SUSPEND;
END
END

--- In firebird-support@yahoogroups.com, dhay@l... wrote:
> Hi,
>
> Any takers on this one (both parts would be great!).
>
> A typical sql call I need to turn into a stored proc is:
>
> SELECT id, title, ExpirationDateTime FROM forms INNER JOIN
formcategory ON forms.id=formcategory.form_id WHERE category_id=1
ORDER BY UpperTitle;
>
> where the category_id will vary. How do I get a result set back?
>
> Thanks for any help,
>
> David
>
>
>
>




To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com



Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/