Hello Eddie,

> 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.
>

The variable needs to be an output parameter in order to be in the resultset
of your SELECT * FROM FINDSP

So:

create procedure myproc returns (ipaddress char(39))
as
...


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