Hello!

FB 2.5 SS on Win7 Home Premium.

I have a stored proc with the following content:
SELECT COUNT(ID) FROM $TBL WHERE FIELD_1 = :PARAM1 AND FIELD_2 = : PARAM_2 .
Let's call it, $SP.

Now I have a view with several joined tables.
This view has the following format:

SELECT ID1, ID2, [FIELDS HERE] FROM...
ORDER BY [ OTHER ORDERS]

The result I want to obtain is to order the rows by the result of $SP
as first order.
Then others would be included.

Ideally, this would be my first choice:

SELECT ID1, ID2,[ FIELDS HERE] FROM...
ORDER BY $SP(ID1, ID2),[OTHER ORDERS]

Alternatively to that, I would settle for this:

SELECT ID1, ID2, $SP(ID1, ID2), [FIELDS HERE] FROM...
ORDER BY 3,[OTHER ORDERS]

Alas, none of the above works.
$SP exists in the db and is compiled and working.

From what I could read on google, it seems that you can only use an SP in a
select when it returns multiple rows, which is not my case(returns a count).

Do you know of any way to obtain the result I long for?
If it cannot be obtained because of the aforementioned limitation, how
would you suggest to solve the problem?

An alternative may be to rewrite the stored along these lines:

SELECT COUNT(ID), FIELD_2 FROM $TBL WHERE FIELD_1 = :PARAM1
GROUP BY FIELD_2

but I am not really keen on this one because the view can potentially grow
quite a lot, making this sort of thing *REALLY* slow and I won't know
till it's too late :)

Can you please provide pointers that won't harm performance when the guns
will be big?

Thank you.

A