Using a variable to specify order by clause in procedure with result set

[JimDiaz]

I would like to specify the order by clause in a procedure which returns a result set with an input variable. I am able to do this with execute immediate but would like to do something like

CREATE PROCEDURE abc.MyTableSelect (
  IN @VariableName CHAR(128)
 )
RESULT (
  A INTEGER,
  B INTEGER,
  C INTEGER
)
BEGIN
  SELECT A, B, C
    FROM MyTable
   ORDER
      BY @VariableName;
END;  

Thanks Jim

[Former Member]

I've simply given my thumbs up on both solutions ...

The 'funny' question here - what kind of client is receiving the result set? Generally, the client has its own capabilities to sort the result sets as well ....

[Former Member · Accepted solution]

Hi Jim,

I know what you want to do, but you can't do that in SQL - components of a SQL statement cannot be dynamically modified. EXECUTE IMMEDIATE is the way.

[JimDiaz · Accepted solution]

Is there any performance penalty to an Execute Immediate statement?

[Breck_Carter · Accepted solution]

Nope... it's one of my favorite statements and it has never showed up as a performance bottleneck... the statement being executed, sure, but not EXECUTE IMMEDIATE wrapper.

[Former Member · Accepted solution]

In the case of a stored procedure, an SQL statement may be a candidate for plan caching which can lead to performance gains by amortizing the compile-time overhead of the statement. Depending on the execution-time expense of the statement, you may not notice the compile-time overhead which is largely what the EXECUTE IMMEDIATE technique will incur.You'll also only notice a difference if the compile-time cost can be amortized over many executions. Also keep in mind that by default the plan cache in SQL Anywhere is 20 plans (statements) per connection, so if your application is complex you are likely not benefiting that much from plan caching.

I would go with Breck's comments and not worry about it. I don't believe the difference will be significant to you.

[Former Member]

Is this point of the stored procedure to avoid writing a statement each time? If so, would it suffice to use EXECUTE IMMEDIATE within the stored procedure?

I.e.

CREATE PROCEDURE abc.MyTableSelect (
  IN @VariableName CHAR(128)
)
RESULT (
  A INTEGER,
  B INTEGER,
  C INTEGER
)
BEGIN
  EXECUTE IMMEDIATE WITH RESULT SET ON
     'SELECT A, B, C FROM MyTable
      ORDER BY ' || @VariableName;
END;

[JimDiaz]

Yes this works but for some reason I try to limit the use of Execute Immediate.

[Breck_Carter]

With therapy, you can learn to love EXECUTE IMMEDIATE 🙂

[VolkerBarth]

FWIW, with V17 you can do that without EXECUTE IMMEDIATE - namely with the help of the newly introduced indirect identifiers.

Here's a small sample with a stored procedure that will query the system catalog and return a result set with owner, table and column names and ids ordered by two columns as specified by means of the procedure's arguments.

Instead of using EXECUTE IMMEDIATE, the statement is parametrized with the particular '[Variablename]' syntax and therefore is much easier to write, read and maintain.

create or replace procedure STP_SysCatalogOrderedBy(in @SortByColumnName1 char(128), in @SortByColumnName2 char(128))
begin
    select user_name, creator, table_name, ST.table_id, table_type,
       column_name, column_id
    from sys.sysuser SU key join sys.systab ST key join sys.systabcol SC
        key join sys.sysuserlist
    order by `[@SortByColumnName1]`, `[@SortByColumnName2]`;
end;

-- some possible calls
call STP_SysCatalogOrderedBy('table_name', 'column_id');
call STP_SysCatalogOrderedBy('column_name', 'table_name');
call STP_SysCatalogOrderedBy('user_name', 'table_name');

-- Note, here both variables must be specified and must be valid column names:

-- Will fail with SQLCODE -1722 ("The variable '@SortByColumnName2' must not be NULL in this context")
call STP_SysCatalogOrderedBy('user_name', null);
 -- will fail with SQLCODE -143 ("Column '' not found')
call STP_SysCatalogOrderedBy('user_name', '')

I guess but am not sure that such predicates should be sargable (see Glenn's original reply).

---

A big thanks to Michael Fischer for sharing that insight at the German SQL Anywhere Developer Day:)

[thomas_duemesnil]

I was also there but didn't recognize you 😉

[VolkerBarth]

Well, I've been sitting second next to the door in the back row, with several contributions to openxml() on deeper levels, DEFAULT GLOBAL AUTOINCREMENT vs. GUIDs, TABLE REFs on views and the like, it that helps as weak hints... - we'll see whether the conference review may contain some pictures of the audience:)

[graeme_perrow]

You can also use a case statement in the order by clause, something like this:

CREATE PROCEDURE abc.MyTableSelect (
   IN @VariableName CHAR(128)
)
RESULT (
  A INTEGER,
  B INTEGER,
  C INTEGER
)
BEGIN
  SELECT A, B, C FROM MyTable
      ORDER BY case @VariableName 
         when 'A' then A
         when 'B' then B
         when 'C' then C
      end case asc;
END;

[Former Member]

Yes, you can do that, but there are tradeoffs. Ivan has described other solutions using the argn() function, which does something similar. The drawback is that these constructions are never sargable which means an index can never be used (in this case, to satisfy the ORDER BY). Depending on the size of MyTable and the size and types of columns A, B, and C, a full sort may well eclipse the additional overhead caused by the use of EXECUTE IMMEDIATE.

I am familiar with other cases where customers have used these constructions for join conditions, with (typically) unsatisfactory results once the tables become large enough (ie production rather than stand-alone testing), again because the join condition isn't sargable and hence the join requires a scan of both inputs.

[VolkerBarth]

While I would recommend the EXECUTE IMMEDIATE road, too, would it help here - if the query can be into a procedure or SQL batch - to split the SELECT statement into separate ones for each order by expression to make these sargable? Such as:

BEGIN
  ...
  IF @VariableName = 'A' THEN
    SELECT A, B, C FROM MyTable ORDER BY A
  ELSEIF @VariableName = 'B' THEN
    SELECT A, B, C FROM MyTable ORDER BY B
  ELSEIF @VariableName = 'C' THEN
    SELECT A, B, C FROM MyTable ORDER BY C
  ELSE
    SELECT A, B, C FROM MyTable ORDER BY A, B, C
  END IF;
END;

(Obviously, it would be rather unhandy with longer statements and/or more order by variants...)

[Former Member]

This isn't desirable from a software engineering standpoint - and is precisely the construction that I think Jim is trying to avoid. It may, however, have more efficient run-time characteristics.

[Breck_Carter]

"isn't desirable"... are you saying it would increase the "technical debt" as described here? http://mags.acm.org/communications/201205?pg=52#pg52

[Former Member]

Yes. That's a great article, Breck - thanks for the link.

[VolkerBarth]

Yes, I fully agree on that point of view, Glenn. I was simply asking for the performance effect...which you have answered:)