At 06:15 PM 11-04-02 +0000, you wrote:

>SP like this one compiles and works pretty well (it returns the
>proper results):
>
>COMMIT WORK;
>SET AUTODLL ON;
>SET TERM ^;
>CREATE PROCEDURE SPS_PROC$GET_RESULTS
> (ACREW_ID SMALLINT,
> ASALESREP_CODE VARCHAR(5))
>RETURNS
> (ASUM_PRICE DECIMAL(25, 4))
>AS
>BEGIN
> SELECT SUM(PRICE) FROM MY_DATA
> WHERE ((:ACREW_ID IS NULL) OR (CREW_ID = :ACREW_ID)) AND
>((:ASALESREP_CODE IS NULL) OR (SALESREP_CODE = :ASALESREP_CODE))
> INTO :ASUM_PRICE;
> SUSPEND;
>END
>^
>COMMIT WORK ^
>SET TERM ; ^
>
>The SP is flexible, in the case I will get the summary result only
>for one crew, I call "SPS_PROC$GET_RESULTS(ACREW_ID, NULL)" (this is
>only pseudo-code), "SPS_PROC$GET_RESULTS(NULL, NULL)" returns summary
>for all data, "SPS_PROC$GET_RESULTS(ACREW_ID, ASALESREP_CODE)"
>returns summary for particular salesrep in particular crew, etc...
>
>MY QUESTION: Why SELECT statement
>
> SELECT SUM(PRICE) FROM MY_DATA
> WHERE ((:ACREW_ID IS NULL) OR (CREW_ID = :ACREW_ID)) AND
>((:ASALESREP_CODE IS NULL) OR (SALESREP_CODE = :ASALESREP_CODE))
>
>don't work alone? I always get compile error
>
>ISC ERROR CODE:335544569
>ISC ERROR MESSAGE:
>Dynamic SQL Error
>SQL error code = -804
>Data type unknown

The parameters (arguments) you pass to stored procedures are constant
values, so the DSQL processor has no way to determine whether the datatype
is correct for comparison with the column. In the stored procedure, you
have declared the data type, so the problem doesn't occur.

>I'm using for IB access IB_SQL (powered by IBObjects) or EMS
>Quickdesk (powered by FIBPlus) and FB RC2...
>Statement like (:APARAM IS NULL) in SELECT WHERE clause is very
>flexible, because in the case of n input parameters I don't have to
>construct 2^n different SELECTs...

I don't know about Quickdesk, but IB_SQL lets you input the parameter
values once the statement is prepared. Assuming you are using the Cursor
tab to test your statements, just prepare the statement then go to the
Params tab and enter the constants for your parameters against each
parameter name.

This is equivalent to applying Params[n].AsSometype or
ParamByName('SomeName').AsSometype in your (Delphi) client program's
BeforeOpen or BeforeExecute statement.

regards,
Helen

All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org ·
http://users.tpg.com.au/helebor/
_______________________________________________________