SQL Anywhere 9.0.2.3228

We often use procedures to return result sets in which the ordered by or
where clause is specified by parameters. To do this we have been wrapping
the entire select statement in an EXECUTE IMMEDIATE.

Is there another easy way to accomplish this? I've provided an example of
how we currently accomplish this in ASA 9.0.2 and some other ways I have
tried and failed.

Thanks

Jim

CREATE PROCEDURE DBA.CustomOrderBy(
IN @OrderBy LONG VARCHAR DEFAULT NULL
)
RESULT(
Column1 INTEGER,
Colomn2 INTEGER
)
BEGIN
DECLARE @SQLStatement LONG VARCHAR;

SET @SQLStatement = 'SELECT Column1, Colomn2 FROM Table1';

IF @OrderBy IS NOT NULL THEN
SET @SQLStatement = @SQLStatement || ' ORDER BY ' || @OrderBy;
END IF;

EXECUTE IMMEDIATE WITH RESULT SET ON @SQLStatement;

END;

--- Helper function used below
CREATE FUNCTION DBA.SQLOrderBy(
IN @OrderBy LONG VARCHAR
)
RETURNS LONG VARCHAR
BEGIN
RETURN ( @OrderBy );
END;

-- Attempt 1
CREATE PROCEDURE DBA.CustomOrderBy(
IN @OrderBy LONG VARCHAR DEFAULT NULL
)
RESULT(
Column1 INTEGER,
Colomn2 INTEGER
)
BEGIN
DECLARE @SQLStatement LONG VARCHAR;

IF @OrderBy IS NULL THEN

SELECT Column1,
Colomn2
FROM Table1;

ELSE

SELECT Column1,
Colomn2
FROM Table1
ORDER
BY DBA.SQLOrderBy(@OrderBy);

END IF;

END;

-- Attempt 2
CREATE PROCEDURE DBA.CustomOrderBy(
IN @OrderBy LONG VARCHAR DEFAULT NULL
)
RESULT(
Column1 INTEGER,
Colomn2 INTEGER
)
BEGIN
DECLARE @SQLStatement LONG VARCHAR;

IF @OrderBy IS NULL THEN

SELECT Column1,
Colomn2
FROM Table1;

ELSE

SELECT Column1,
Colomn2
FROM Table1
ORDER
BY !@OrderBy

END IF;

END;

You could try and 'hardcode' the actual 'ORDER BY' in your procedure.
Advantage of this approach would be that stored procedure once executed
would
get compiled and cached, so each consequtive run would not require
compilation
therefore small improvement in performance. Your SQL would also be more
readable,
therefore easier to debug etc.

Disadvantage, it's not so generic and it would require extra maintanance
with
schema/spec changes.

e.g.

CREATE PROCEDURE DBA.CustomOrderBy(
IN @OrderBy LONG INTEGER DEFAULT NULL
)
RESULT(
Column1 INTEGER,
Column2 INTEGER
)
BEGIN

IF @OrderBy IS NULL THEN

SELECT Column1,
Column2
FROM Table1;

ELSEIF @OrderBy = 1 THEN

SELECT Column1,
Column2
FROM Table1
ORDER BY 1;

ELSEIF @OrderBy = 2 THEN
SELECT Column1,
Column2
FROM Table1
ORDER BY 2;

END IF;


Lucjan


"Jim Diaz" <nospam (AT) emprisecorporation (DOT) com> wrote

Lucjan's approach is a good one if you have a known set of orders.

If the full set of possible orderings is unknown or too large but there is a
known set of ORDER BY columns, you may find the ARGN expression of use:

create variable @obcol int;
set @obcol = 3;
select *
from sysdomain
order by argn( @obcol, domain_id, domain_name, type_id, "precision" )


For example, your ARGN could include all of the SELECT list elements, and
the first parameter would be the ordinal position to select. If you want
multiple order-by elements, you can repeat the ARGN with different input
parameters. Using an ordinal position of 0 or otherwise outside the list
makes the expression NULL, so you can use that value for unused elements.

Depending on which version of SQL Anywhere you are using, the optimizer can
"fold" an ARGN expression if the first parameter is known at open time so
that it is equivalent to to replacing the expression with the selected
element, so plans with indexes are considered to satisfy the ORDER BY. I
think this optimization is not present in 9.0.2, so it will mean a SORT is
always generated even if the order by could have instead been satisfied by
an index.

Another thing to consider is that the ARGN expression finds a common
expression type and promotes all arguments to this type. This may not match
what you want.

In the WHERE clause, a pattern I see sometimes is a predicate such as "WHERE
( ? IS NULL or T.col = ? )". With host variables, you may prefer to use
aliases to avoid duplicating the host variables:

create variable @type_id int;
set @type_id = 8;
--set @type_id = null;

with P as ( select @type_id as want_type_id from sys.dummy as Parameters )
select *
from P, sysdomain
where (want_type_id is null or type_id = want_type_id)

On the other hand, duplicating host variables means that you can have an
indicator for whether the predicate should apply, and this indicator is
distinct from any values needed for the predicate.

The optimizer recognizies which predicates survive if it can evaluate the
variables at open time, and unused predicates (where the parameter is NULL)
are "boiled away".

One thing to be aware of with both the ARGN and dynamic WHERE clause
elements is that they do not work well with the server's plan caching. The
plan caching will occasionally try to generate a plan without looking at the
value of any variables, and this can leave the predicates in the WHERE
clause as ORs that are inefficient. Likewise, the ARGN can be left
unoptimized even in versions that support this. For these reasons, it may be
best to disable plan caching (set Max_plans_cached=0 locally in the
procedure or, preferably, add an OPTION clause in version 10.0 and above
specifying "OPTION (FORCE OPTIMIZATION)". The latter is preferred where
supported because it only disables plan caching for the problematic
statement, leaving other statements to benefit from plan caching.

--
Ivan T. Bowman
SQL Anywhere Research and Development
[Sybase iAnywhere]

"Lucjan Chmura [Sybase iAnywhere]" <lchmura_at_sybase_dot_com> wrote in
message news:4ac64dd0$1 (AT) forums-1-dub (DOT) ..

Thanks very much for this info. I have tried both with great success..

Jim

"Lucjan Chmura [Sybase iAnywhere]" <lchmura_at_sybase_dot_com> wrote in
message news:4ac64dd0$1 (AT) forums-1-dub (DOT) ..