At 04:06 PM 31-07-02 +0000, you wrote:

>The following SP works, but I want to do some additional selection
>inside the SP.
>The input parameter CORP is 'C' for customers, 'P' for prospects
>and blank for either.
>The code inside the comments kills IBADMIN.

Ermmm...yes, it would. SQL and PSQL are formal languages - you can't just
pop in free-form sentences into the syntax and expect it to work.

>Can someone please point me in the right direction?
>I would also like to order by conditionally using an input parameter.
>Is this something that can be accomplished?

No. Stored procedures are validated and compiled when you create
them. The compiler must know and validate the "size and shape" of the
metadata at compile-time. Thus, you can parameterize constant values but
not the metadata.

If you have a selectable SP, you can order the output when you call it:

SELECT field1, field2, field3 from Myproc(Arg1)
order by 2

although this will be slow on a big output set because it won't be able to
use an index on the output of the SP and it will have to wait until the SP
completes before it begins to sort.

Also, even in a direct SQL statement, you can't define "conditional" output
column sets.

>REATE PROCEDURE CUSTSELECT_CUSTID (CORP Char(1))
>returns (CUSTID Char(10),
> COMPANY Char(60),
> ZIP Char(14),
> CUSTPROS Char(1),
> ZIPCODE Char(14),
> ZIPCITY Char(40),
> ZIPSTATE Char(24))
>AS
>
>begin
> for
> select C.CUSTID, C.COMPANY, C.ZIP, C.CUSTPROS,
> Z.ZIPCODE, Z.ZIPCITY, Z.ZIPSTATE
> from CUSTOMER C join ZIPCODE Z on Z.ZIPCODE = C.ZIP
>/* if :CORP <> '' then
> begin
> and C.CUSTPROS = :CORP
> suspend;
> end; */
> order by CUSTID
> into :CUSTID, :COMPANY, :ZIP, :CUSTPROS, :ZIPCODE, :ZIPCITY,
>ZIPSTATE
> do
> suspend;
>
>end

The right way to handle this is to work with the values once you have them
in hand.
....
begin
for
select C.CUSTID, C.COMPANY, C.ZIP, C.CUSTPROS,
Z.ZIPCODE, Z.ZIPCITY, Z.ZIPSTATE
from CUSTOMER C join ZIPCODE Z on Z.ZIPCODE = C.ZIP
order by CUSTID
into :CUSTID, :COMPANY, :ZIP, :CUSTPROS, :ZIPCODE, :ZIPCITY,
ZIPSTATE
do
begin
if CORP <> '' then
begin
CUSTPROS = CORP ;
suspend;
end
end
end

Even better would be to use your SQL statement to eliminate the unwanted
rows from your output set. In the example you provide here, you don't need
a stored procedure to get this output set:

select C.CUSTID, C.COMPANY, C.ZIP, C.CUSTPROS,
Z.ZIPCODE, Z.ZIPCITY, Z.ZIPSTATE
from CUSTOMER C join ZIPCODE Z on Z.ZIPCODE = C.ZIP
where C.CUSTPROS = :CORP
and C.CUSTPROS <> ''
and C.CUSTPROS is not null
order by C.CUSTID

Notice that "blank" (empty string) and null are not the same. Unless
CUSTPROS is a not null column, you will need to handle the null condition.

Also, look into the VARCHAR() type. One does not normally use CHAR() for
strings that are not required to completely fill the column. In this
example, only ZIPCODE and CUSTPROS would be CHAR() candidates.

heLen

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