Ghanshyam,

Here is an example:

create procedure MyProc ( InParam1 integer )
returns ( OutParam1 integer, OutParam2 as varchar(10))
as
begin
for select Field1, Field2 from MyTable
where TheKey = :InParam1
into :OutParam1, :OutParam2

do suspend;
end

--- In firebird-support@yahoogroups.com, "Ghanshyam"
<ghanshyam_patil@m...> wrote:

> Hello,
> When I'm trying to create a SP (Having union of 3 nested select
> statements) by executing the following code (The entire code is given
> below), it gives me an error -
>
> "Token unknown - SUSPEND"
>
> iF SUSPEND is removed, it gives error "Token unknown - END"
> i.e anything written after last SELECT is an error. May be last
> SELECT is having some problem. Or is there any other problem related
> to UNION?
>
>
> CREATE PROCEDURE showlocation(taxno INTEGER)
> AS
> BEGIN
> SELECT tblcountry.country || '/' || tblstate.state || '/' ||
> tblcounty.county AS location ,tblcounty.countyid AS locid ,'1' AS
> name
> FROM tblcounty,tblstate,tblcountry WHERE tblcountry.countryid =
> tblstate.countryid AND
> tblcounty.stateid = tblstate.stateid AND countyid IN
> (SELECT tblcounty.countyid FROM tblcounty,tbltaxcounty WHERE
> tblcounty.countyid = tbltaxcounty.countyid AND
> tbltaxcounty.taxid=:taxno)
> UNION
> SELECT tblcountry.country || '/' || tblstate.state AS
> location ,tblstate.stateid AS locid ,'2' AS state
> FROM tblstate,tblcountry WHERE tblcountry.countryid =
> tblstate.countryid AND
> tblstate.stateid IN ( SELECT tblstate.stateid FROM
> tblstate,tbltaxstate WHERE tblstate.stateid = tbltaxstate.stateid AND
> tbltaxstate.taxid=:taxno)
> UNION
> SELECT country,countryid AS locid ,'3' AS country FROM tblcountry
> WHERE countryid IN ( SELECT tblcountry.countryid FROM
> tblcountry,tbltaxcountry WHERE tblcountry.countryid =
> tbltaxcountry.countryid AND tbltaxcountry.taxid=:taxno)
> SUSPEND;
> END
>
>
> Thanx in advance for any help.
>
> Regards,
> Ghanshyam.