Martijn, et al,

Thanks, with your quick reply you have allowed me to unearth a ton of syntax that I was missing in creating Firebird Stored proceedures.

I have now created my first executaeable proceedure but it does not return any records...I think I need just another small nudge to be self sufficent again!

SQL query that I want to minic...
----------------------------------
SELECT
a.IP_ADDRESS, a.SUBNET, a.CIDR_MASK, a.IP_VERSION, a.SHORT_IP_ADDRESS, a.ALIVE, a.MAC_ADDRESS, a.ADDRESS_TYPE, a.DEVICE_TYPE, a.REPLICATED_FROM, a.HOST_NAME, a.OWNER, a.LOCATION, a.TELEPHONE_NUMBER, a.EMAIL_ADDRESS, a.NOTES, a.DNS_NAME, a.SYSTEM_NAME, a.CLIENT_MNEMONIC, a.SERIAL_NUMBER
FROM
ADDRESSES a
WHERE
SUBNET = '159.140.065.000'
ORDER BY
IP_ADDRESS
---------------------------------------

Stored Proceedure that I have written which compiles but upon execution does not reurn any records or data...
----------------------------------------
CREATE PROCEDURE FINDSP
AS
DECLARE VARIABLE ipaddress Char(39);
BEGIN
FOR
SELECT
a.IP_ADDRESS
FROM
ADDRESSES a
WHERE
SUBNET = '159.140.065.000'
ORDER BY
IP_ADDRESS
INTO :ipaddress
DO SUSPEND;
END
------------------------------------------


The strait query returns a few dozen records, the SP returns nothing at all.

Ultimately I would like the user of the Stored Proceedure to supply the SUBNET value, so any hints on how to tackle that would be great, but I should be able to manage that one on me own.

Thanks all,

Eddie Dressler
DBA
Cerner Corporation

--- In firebird-support@yahoogroups.com, "Dressler,Eddie" <Eddie.Dressler@...> wrote:
>
> Martijn,
>
>
>
> Ah, that might be the issue. Perhaps some background would help...
>
>
>
> In this stored proc what I want to do is return all data referenced by columns listed in the SELECT clause based on the condition in the WHERE clause. I am used to using INTO when I am inserting data into a different or temp table (Transact SQL syntax). Does Firebird require you to use an INTO statement even if you are only returning records?
>
>
>
> Thanks,
>
>
>
> Eddie
>
>
>
>
>
> From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Martijn Tonies
> Sent: Friday, October 09, 2009 11:33 AM
> To: firebird-support@yahoogroups.com
> Subject: Re: [firebird-support] Simple Firbird syntax error
>
>
>
>
>
> Hi,
>
> > Im new to Firebird and am trying to create a simple paramaterized
> > proceedure. I am interfaceing with an EasyIP database and need a little
> > help. My code is thus...
> >
> > -------------------------------------------------
> > CREATE PROCEDURE MySP (subnetmask char(128))
> > AS
> > BEGIN
> > SELECT
> > a.IP_ADDRESS, a.SUBNET, a.CIDR_MASK, a.IP_VERSION, a.SHORT_IP_ADDRESS,
> > a.ALIVE, a.MAC_ADDRESS, a.ADDRESS_TYPE, a.DEVICE_TYPE, a.REPLICATED_FROM,
> > a.HOST_NAME, a.OWNER, a.LOCATION, a.TELEPHONE_NUMBER, a.EMAIL_ADDRESS,
> > a.NOTES, a.DNS_NAME, a.SYSTEM_NAME, a.CLIENT_MNEMONIC, a.SERIAL_NUMBER
> > FROM
> > ADDRESSES a
> > WHERE
> > SUBNET = :subnetmask
> > ORDER BY
> > IP_ADDRESS;
> > END
> > -------------------------------------------------------
> >
> > My Dev enviroment is RazorSQL and anytime i try to execute that create
> > statment I get the following error (Line 11 is IP_ADDRESS;)...
> >
> > ERROR: GDS Exception. 335544569. Dynamic SQL Error
> > SQL error
> > code = -104
> > Token unknown - line 11, column 13
> > ;
> >
> > Query = CREATE
> > PROCEDURE Eddie (subnetmask char(128))
> > AS
> > BEGIN
> > SELECT
> >
> > a.IP_ADDRESS, a.SUBNET, a.CIDR_MASK, a.IP_VERSION, a.SHORT_IP_ADDRESS,
> > a.ALIVE, a.MAC_ADDRESS, a.ADDRESS_TYPE, a.DEVICE_TYPE,
> > a.REPLICATED_FROM, a.HOST_NAME, a.OWNER, a.LOCATION,
> > a.TELEPHONE_NUMBER, a.EMAIL_ADDRESS, a.NOTES, a.DNS_NAME,
> > a.SYSTEM_NAME, a.CLIENT_MNEMONIC, a.SERIAL_NUMBER
> > FROM
> >
> > ADDRESSES a
> > WHERE
> > SUBNET = :subnetmask
> > ORDER BY
> > IP_ADDRESS;
> > END
> >
> >
> >
> >
> > If I remove the last semicolon I get the same error but one line has
> > changed...
> > Token unknown - line 12, column 1 (line 12 is END)
> >
> > Any help is greatly appreciated.
>
> This needs an INTO clause, where do the selected columns go?
>
> With regards,
>
> Martijn Tonies
> Upscene Productions
> http://www.upscene.com
>
> Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
> Anywhere, MySQL, InterBase, NexusDB and Firebird!
>
> Database questions? Check the forum:
> http://www.databasedevelopmentforum.com
>
>
>
> ----------------------------------------------------------------------
> CONFIDENTIALITY NOTICE This message and any included attachments are from Cerner Corporation and are intended only for the addressee. The information contained in this message is confidential and may constitute inside or non-public information under international, federal, or state securities laws. Unauthorized forwarding, printing, copying, distribution, or use of such information is strictly prohibited and may be unlawful. If you are not the addressee, please promptly delete this message and notify the sender of the delivery error by e-mail or you may call Cerner's corporate offices in Kansas City, Missouri, U.S.A at (+1) (816)221-1024.
>
>
> [Non-text portions of this message have been removed]
>