You're right Daniel, it is simple even though it can be made more
complicated and hide the simple solution by introducing EXECUTE
IMMEDIATE.


SET TERM ^;
CREATE PROCEDURE SMART_SEARCH (
USER VarChar(255),
PRIVATE SmallInt)
RETURNS (
FIRST_NAME VarChar(255))
AS
BEGIN
IF( :PRIVATE = 0 )
BEGIN
SELECT
FIRST_NAME
FROM
MYTABLE
WHERE (USER = :USER AND PRIVATE = :PRIVATE)
INTO
:FIRST_NAME;
END
ELSE
IF( :PRIVATE = 1 )
BEGIN
SELECT
FIRST_NAME
FROM
MYTABLE
WHERE (USER = :USER OR PRIVATE = :PRIVATE)
INTO
:FIRST_NAME;
END
END^

Set

--- In firebird-support@yahoogroups.com, "Daniel Jimenez" wrote:
> Hi,
>
> I am not feeling to smart today, as I am not able to solve a very
> simple issue with an execute procedure. I am hoping that someone can
> help, after they stop laughing.
>
> So say:
>
> I would like a procedure that would based on the input, build a
> WHERE clause i.e
> IF( :PRIVATE = 0 )
> WHERE (USER = :USER AND PRIVATE = :PRIVATE)
> IF( :PRIVATE = 1 )
> WHERE (USER = :USER OR PRIVATE = :PRIVATE)
>
> SET TERM ^;
> CREATE PROCEDURE SMART_SEARCH (
> USER VarChar(255),
> PRIVATE SmallInt)
> RETURNS (
> FIRST_NAME VarChar(255))
> AS
> BEGIN
> SELECT
> FIRST_NAME
> FROM
> MYTABLE
>
> // SOMEHOW COMPLETE THE QUERY USING THE IF STATEMENTS MENTION ABOVE
>
> INTO
> :FIRST_NAME;
> END^