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

asked 18 May '12, 12:23

J%20Diaz's gravatar image

J Diaz
830243044
accept rate: 14%

retagged 18 Nov '12, 10:28

Nica%20_SAP's gravatar image

Nica _SAP
866722

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 ....

(20 May '12, 13:16) Frum Dude

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.

permanent link

answered 18 May '12, 12:34

Glenn%20Paulley's gravatar image

Glenn Paulley
10.7k568104
accept rate: 43%

Is there any performance penalty to an Execute Immediate statement?

(18 May '12, 18:01) J Diaz
Replies hidden

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.

(18 May '12, 20:55) Breck Carter

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.

(18 May '12, 22:03) Glenn Paulley

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;
permanent link

answered 18 May '12, 15:34

Tyson%20Lewis's gravatar image

Tyson Lewis
2.2k1641
accept rate: 22%

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

(18 May '12, 18:02) J Diaz
Replies hidden

With therapy, you can learn to love EXECUTE IMMEDIATE :)

(18 May '12, 20:56) Breck Carter

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;
permanent link

answered 18 May '12, 23:44

Graeme%20Perrow's gravatar image

Graeme Perrow
8.4k369107
accept rate: 51%

2

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.

(19 May '12, 16:34) Glenn Paulley
Replies hidden

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...)

(20 May '12, 14:26) Volker Barth

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.

(21 May '12, 07:30) Glenn Paulley
2

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

(21 May '12, 09:39) Breck Carter

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

(21 May '12, 14:05) Glenn Paulley

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

(22 May '12, 04:02) Volker Barth
showing 1 of 6 show all flat view

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:)

permanent link

answered 22 Apr '16, 10:35

Volker%20Barth's gravatar image

Volker Barth
29.5k291441646
accept rate: 32%

edited 22 Apr '16, 10:37

I was also there but didn't recognize you ;-)

(23 Apr '16, 02:28) Thomas Dueme...
Replies hidden

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:)

(23 Apr '16, 06:00) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×101
×13
×10

question asked: 18 May '12, 12:23

question was seen: 2,044 times

last updated: 23 Apr '16, 06:00