If you are using the code in client side queries, you
may have to use different parameter names, I would imaging that 2 of the
same name would confuse the client side components, they are not like
variables in SP.

is that what you mean by > don't work alone? I always get compile error


JAC

""tibotaka"" <belan@...> wrote in message
news:a94jrd+jl9k@......

> 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
>
> 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...
>
> Thanks for your answer in advance and sorry for my english...
>
> Tibor
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>
>