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;
I know what you want to do, but you can't do that in SQL - components of a SQL statement cannot be dynamically modified.
answered 18 May '12, 12:34
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?
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;
answered 18 May '12, 15:34
You can also use a
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;
answered 18 May '12, 23:44
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 '
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:)