Why are procedure names not allowed for indirect identifiers?

[VolkerBarth]

We like the "Indirect identifier" feature of SQL Anywhere 17 when dealing with varying table schemata, as it helps to omit dynamic SQL.

Now I've stumpled upon the documented limitation that – besides table and column names – indirect identifiers are supported with the "new" object types mutexes and semaphores but not with procedure and function names.

Why is that limitation? IMHO, as procedures are often used like base tables and views within complex queries (where dynamic SQL often gets difficult), it seems worthwhile to use indirect identifiers for those, too.

---

The following sql block when run in the SQL Anywhere Demo database raises SQLCODE -131 (Syntax error near '`[strProcName') when using indirect identifiers with procedure calls.

begin
   declare strOwnerName varchar(128) = 'GroupO';
   declare strProcName varchar(128) = 'ShowContacts';
   select strOwnerName, strProcName;

   call GroupO.ShowContacts(); -- succeeds
   select * from GroupO.ShowContacts() MyProc; -- succeeds

   call `[strOwnerName]`.`[strProcName]`(); -- returns SQLCODE=-131
   select * from `[strOwnerName]`.`[strProcName]`() MyProc;  -- returns SQLCODE=-131
end;

[VolkerBarth]

FWIW, as a partial workaround, I can declare a temporary procedure within a SQL block/procedure that uses EXECUTE IMMEDIATE WITH RESULT SET ON to call the desired procedure, such as

begin
   declare strOwnerName varchar(128) = 'GroupO';
   declare strProcName varchar(128) = 'ShowContacts';

   drop procedure if exists MyTempProc;
   create temporary procedure MyTempProc(
      strOwnerNameParam varchar(128),
      strProcNameParam varchar(128),
      nContactIDParam int default null)
   result (ID integer, Surname person_name_t, GivenName person_name_t, Title person_title_t,
           Street street_t, City city_t, "State" state_t, Country country_t, PostalCode postal_code_t,
           Phone phone_number_t, Fax phone_number_t)
   begin
      execute immediate with result set on
         'call [' || strOwnerNameParam || '].[' || strProcNameParam || '](' || nContactIDParam || ')';
   end;

   select * from MyTempProc(strOwnerName, strProcName, default);
end;   

and then use that temporary procedure within further joins...

The drawback is the need to specify required parameters and result set columns. Unless one does except a particular static scheme (as in my sample), in order to be flexible, one would need to create the temporary procedure's definition itself via dynamic SQL based on the contents of SYSPROCPARM(S). That could itself get confusing...

---

Conclusion: I guess the need for SQL Anywhere to know a procedure's meta data before it is used within a SELECT query is also the reason procedure names are not allowed for indirect identifiers...

[former_SQLA_member1694955]

The restriction you experienced with SQL Anywhere 17 is most likely due to the fact that procedures and functions are not the same as tables, columns, mutexes, or semaphores. Tables and columns are data objects, mutexes, and semaphores are synchronization objects, and procedures and functions are executable code objects.

When indirect identifiers are used with tables, columns, mutexes, and semaphores, the syntax is resolved at build time and the object is accessible at runtime. When using indirect identifiers with procedures and functions, the syntax is resolved at runtime as well, but the code object is performed at runtime. This adds complexity and possible security vulnerabilities since the database engine must dynamically compile and execute code that is only partially written.

It's worth mentioning that SQL Anywhere 17 supports the use of variables in procedure and function calls, which can give some flexibility when working with different table schemata. For example, you may use the EXECUTE IMMEDIATE command to run a stored procedure with a variable name dynamically:

DECLARE @proc_name VARCHAR(128);
SET @proc_name = 'my_proc';

EXECUTE IMMEDIATE 'CALL ' || @proc_name || '();';

At runtime, this syntax produces the procedure call as a string and then executes it with the EXECUTE IMMEDIATE instruction. While this does need dynamic SQL, it can be useful in circumstances where indirect IDs cannot be utilized with procedures and functions.