FireBird: How do you nest a select in an if?

I'm very new to FireBird, but I want to know how I can use a select statement as part of my conditional criteria. I feel like I've been to the internet in back trying to find a way to do this, but haven't come up with much. Below is my attempt at getting this to work. Thanks in advance for any help.

SET TERM ^ ;
ALTER PROCEDURE sp_test (
IPADD Varchar(32),
HN Varchar(32),
NOTE Varchar(200) )
RETURNS ( update_count integer )
AS
BEGIN
IF((SELECT COUNT(*)
    FROM ADDRESSES a
    WHERE a.ADDRESS_TYPE = 'Reserved'
      AND a.ALIVE = 'N'
   开发者_运维百科   AND (a.HOST_NAME = '' OR a.HOST_NAME is NULL)
      AND (a.DNS_NAME = '' OR a.DNS_NAME is NULL)
      AND (a.SYSTEM_NAME = '' OR a.SYSTEM_NAME is NULL)) > 0)
THEN
  UPDATE 
    ADDRESSES a
  SET
    a.HOST_NAME = :HN,
    a.ADDRESS_TYPE = 'Assigned',
    a.NOTES = :NOTE
  WHERE
    a.SHORT_IP_ADDRESS = :IPADD;
  update_count = 1;
  SUSPEND;
ELSE
  update_count = 0;
  SUSPEND;
END^
SET TERM ; ^

GRANT EXECUTE
ON PROCEDURE sp_test TO  SYSDBA;

Using COUNT to check is there records to update is not the best way, use EXISTS instead, ie your IF would be

IF(EXISTS(SELECT 1 FROM ADDRESSES a
    WHERE a.ADDRESS_TYPE = 'Reserved'
      AND a.ALIVE = 'N'
      AND (a.HOST_NAME = '' OR a.HOST_NAME is NULL)
      AND (a.DNS_NAME = '' OR a.DNS_NAME is NULL)
      AND (a.SYSTEM_NAME = '' OR a.SYSTEM_NAME is NULL)))
THEN

But there seems to be a problem with your return value, update_count - you return 1 if you execute the UPDATE, but the actual number of rows affected by the statement might be something else. I suggest you use ROW_COUNT context variable instead. So your procedure would be

ALTER PROCEDURE sp_test (
IPADD Varchar(32),
HN Varchar(32),
NOTE Varchar(200) )
RETURNS ( update_count integer )
AS
BEGIN
  IF(EXISTS(SELECT 1 FROM ADDRESSES a
    WHERE (a.ADDRESS_TYPE = 'Reserved')
      AND (a.ALIVE = 'N')
      AND (a.HOST_NAME = '' OR a.HOST_NAME is NULL)
      AND (a.DNS_NAME = '' OR a.DNS_NAME is NULL)
      AND (a.SYSTEM_NAME = '' OR a.SYSTEM_NAME is NULL)))
  THEN BEGIN
     UPDATE ADDRESSES a SET
       a.HOST_NAME = :HN,
       a.ADDRESS_TYPE = 'Assigned',
       a.NOTES = :NOTE
     WHERE a.SHORT_IP_ADDRESS = :IPADD;
     update_count = ROW_COUNT;
  END ELSE update_count = 0;
  SUSPEND;
END^