SELECT SP's newbie question

Hi, this is my first post here and I'm new with Firebird too.

I'm trying to migrate from MSDE to Firebird but I'm having some problems. I'm using EMS to create my database tables and stored procedures. I'm having problems with stored procedures that involve SELECT statements.

For example, this does not compile:

CREATE PROCEDURE S_CLIENTES (
P_CLIENTEID INTEGER
, P_CIF CHAR(9)
, P_RAZONSOCIAL VARCHAR(255)
)
AS
BEGIN
SELECT
CLIENTES.CLIENTEID
, CLIENTES.CIF
, CLIENTES.RAZONSOCIAL
, CLIENTES.DIRECION
, CLIENTES.CODIGOPOSTAL
, CLIENTES.POBLACION
, CLIENTES.TELEFONO
, CLIENTES.FAX
, CLIENTES.WEB
, CLIENTES.OBSERVACIONES
FROM CLIENTES
WHERE (( :P_CLIENTEID IS NULL) or (CLIENTEID = :P_CLIENTEID))
AND (( :P_CIF IS NULL) or (CIF = :P_CIF))
AND (( :P_RAZONSOCIAL IS NULL) or (RAZONSOCIAL LIKE '%' || :P_RAZONSOCIAL || '%'))
SUSPEND;
END

I think it's related to the SUSPEND word ¿how it works?
I've read in docs that this kind of stored procedures needs the RESULT block but, what I have to put there in this case?

Thanks in advance.

Can you post the error message that you receive while running this ?

Ok, seems that the SELECTs must have this format:

CREATE PROCEDURE MYPROC
RETURNS ( ReturnField1 integer, ReturnField2 integer, ReturnField3 integer)
AS
FOR SELECT c1, c2, c3
FROM myTable
WHERE myCondition
INTO :ReturnField1, :ReturnField2, :ReturnField3
DO
BEGIN
SUSPEND;
END


Is that way?

What if my SELECT has 40 fields, I must declare 40 vars in the RETURNS section !!??

Correct.

Correct. Or use IBExpert, it will generate StoredProc for you

--
Best regards,
Fikret Hasovic http://fikret.fbtalk.net

FirebirdSQL Foundation member.
- Join today at http://www.firebirdsql.org/ff/foundation
JEDI VCS contributor
http://jedivcs.sourceforge.net/