Filter in a Stored Procedure

I have the following Stored Procedure:

CREATE PROCEDURE return_company_data
(
PSTATE VARCHAR(10)
)
RETURNS (
CODE INTEGER,
NAME VARCHAR(10),
STATE VARCHAR(10))

AS
BEGIN
FOR
SELECT CODE, NAME, STATE
FROM COMPANY
INTO :CODE, :NAME, :STATE
DO
SUSPEND;
END

I want to change this Stored Procedure in which if the parameter "PSTATE" is not blank it adds to the select the line: "WHERE STATE = :PSTATE". I tried putting:
IF (PSTATE <> '') THEN
WHERE STATE = PSTATE

But it doesn´t work. How can I solve this ??? Thanx

If your PSTATE field is a varchar(1) or a field that contains no similar data (ex: PSTATE IN (a,b,c,d,h,x,y)) you can try this instead:


USING "STARTING WITH", IF PARAMETER IS NULL WILL RETURN ALL THE DATA, ELSE IT WILL RETURN THE FIELD THAT CONTAINS THE PARAMETER.

This is "work-around" for your problem that will not work for a PSTATE field like "syn","sync","synch","synchr","synchro" because if you run a SELECT .... WHERE PSTATE STARTING WITH "syn" it will return all the fields.

Hope this helps.

If you don't mind to test for null you can do:

I use my "Filter Search" Procedures like this.. works fine for me.. ONLY NOT WORK with BLOB(TEXT) field´s:

SET TERM ^ ;
CREATE PROCEDURE aspEquipeSelSrch
(
"iidEquipe" INTEGER,
"inome" VARCHAR(150),
"ihomePage" VARCHAR(100),
"iemail" VARCHAR(150)
)
RETURNS
(
"idEquipe" INTEGER,
"nome" VARCHAR(150),
"homePage" VARCHAR(100),
"email" VARCHAR(150)
)
AS
BEGIN
FOR
SELECT
"idEquipe",
"nome",
"homePage",
"email"
FROM "Equipe"
WHERE
COALESCE("Equipe"."idEquipe", 0) = COALESCE(:"iidEquipe", "Equipe"."idEquipe", 0) AND
COALESCE("Equipe"."nome", '') = COALESCE(:"inome", "Equipe"."nome", '') AND
COALESCE("Equipe"."homePage", '') = COALESCE(:"ihomePage", "Equipe"."homePage", '') AND
COALESCE("Equipe"."email", '') = COALESCE(:"iemail", "Equipe"."email", '')
INTO
:"idEquipe",
:"nome",
:"homePage",
:"email"
DO
BEGIN
SUSPEND;
END
END
^
SET TERM ; ^
GRANT SELECT ON "Equipe" TO PROCEDURE aspEquipeSelSrch;
GRANT EXECUTE ON PROCEDURE aspEquipeSelSrch TO SYSDBA;