CODEWITHSUNDEEP
sqlselectfirebird
dthrasher
dthrasher

Reputation: 41842

Selecting a scalar value in a Firebird stored procedure

I'd like to get the ID of a particular row from within a Firebird 2.1 stored procedure. I can't seem to remember the syntax, and it's driving me nuts.

This is how the code might look in TSQL, used by Microsoft SQL Server. 

    @ID = SELECT ID FROM ADDRESS WHERE 
      POBox = :POBOX AND
      ExtendedAddress = :EXTENDEDADDRESS AND
      StreetAddress = :STREETADDRESS AND
      Locality = :LOCALITY AND
      Region = :REGION AND
      PostalCode = :POSTALCODE AND
      CountryName = :COUNTRYNAME;

What's the Firebird equivalent?

UPDATE: The Firebird SQL migration guide suggests that this should work

    SELECT ID INTO :ID FROM ADDRESS WHERE 
      POBox = :POBOX AND
      ExtendedAddress = :EXTENDEDADDRESS AND
      StreetAddress = :STREETADDRESS AND
      Locality = :LOCALITY AND
      Region = :REGION AND
      PostalCode = :POSTALCODE AND
      CountryName = :COUNTRYNAME;

But I get a syntax error at "INTO" when I try to create the stored proc.

Upvotes: 0

Views: 709

Answers (1)

dthrasher
dthrasher

Reputation: 41842

Apparently, the Firebird migration guide is wrong. This syntax worked for me:

SELECT ID FROM ADDRESS WHERE 
   POBox = :POBOX AND
   ExtendedAddress = :EXTENDEDADDRESS AND
   StreetAddress = :STREETADDRESS AND
   Locality = :LOCALITY AND
   Region = :REGION AND
   PostalCode = :POSTALCODE AND
   CountryName = :COUNTRYNAME
INTO :ID;

Note that the INTO clause must come after the FROM and WHERE clauses.

Upvotes: 4

Related Questions