How to create procedure with DESCRIBE statement WITH VARIABLE RESULT clause

[Former Member]

I need to create procedure without a RESULT clause, that returns a variable result sets.

I know I can use DESCRIBE statement and WITH VARIABLE RESULT clause, but I do not understand how write syntax.

I use SQL Anywhere 12.

http://dcx.sap.com/1201/en/dbusage/ug-proctrig-sectb-5471523.html http://dcx.sap.com/1201/en/dbreference/describe-esql-statement.html

CREATE OR REPLACE PROCEDURE Names()
BEGIN
// how to use here DESCRIBE WITH VARIABLE RESULT ???

  if VAREXISTS('connection_variable_abc')=0 then
      create variable connection_variable_abc integer;
  end if;

   IF connection_variable_abc = 1 THEN
      SELECT Surname, GivenName
      FROM Employees
   ELSE
      SELECT GivenName
      FROM Employees
   END IF
END;


CREATE OR REPLACE PROCEDURE NamesV2()
BEGIN
// how to use here DESCRIBE WITH VARIABLE RESULT ???

   EXECUTE ( 'SELECT * FROM Names()' );
END;

[VolkerBarth · Accepted solution]

I think there's a misunderstanding here. If a procedure does need to supply variable result sets, you have to DESCRIBE the result set not within the procedure itself but when calling it (and only when the according API does require that, e.g. for ODBC it is not necessary).

Is there a particular reason you use an EXECUTE statement to call Names() in procedure NamesV2()? You could also use static SQL here with a CALL or a SELECT FROM statement (and for the latter, provide an according WITH clause to describe the procedure's result set, here depending on the connection variable's value...)

E.g. the following works when called within DBISQL and returns a result set with one resp. two columns:

CREATE OR REPLACE PROCEDURE NamesV2()
BEGIN
   CALL Names();
END;

-- calling procedure
SET connection_variable_abc = 0;
CALL NamesV2();
SET connection_variable_abc = 1;
CALL NamesV2();

-- using SELECT FROM with WITH clause
SET connection_variable_abc = 0;
SELECT * FROM NamesV2() WITH (GivenName varchar(255));
SET connection_variable_abc = 1;
SELECT * FROM NamesV2() WITH (Surname varchar(255), GivenName varchar(255));

[Former Member · Accepted solution]

Names() definition is different in each procedure call. I was thinking if I use EXECUTE in NamesV2() then Names() definition will be parsed in each procedure call. If I use only select then NamesV2() will parse Names() definition only at 1st run and remember.

[Former Member · Accepted solution]

So for what purpose is the WITH VARIABLE RESULT clause and where we use it?

[VolkerBarth · Accepted solution]

I'd think, as documented, it is part of an Embedded SQL statement and as such not used within the Watcom-SQL syntax.

[VolkerBarth · Accepted solution]

FWIW, I think even an ALTER PROCEDURE NamesV2 RECOMPILE does not modify the information stored about the expected result set. For Names() in your definition, it expects a result set with 2 columns, ignoring the current variable's value.

You can check yourself with the following system catalog query:

SELECT SP.proc_name, SPP.*
FROM SYSPROCEDURE SP KEY JOIN SYSPROCPARM SPP
WHERE proc_name IN ('Names', 'NamesV2')
ORDER by proc_name, parm_id;

If you alter the Names() procedure by swapping the conditional branches, only one result column will be expected for Names():

CREATE OR REPLACE PROCEDURE Names()
BEGIN
// how to use here DESCRIBE WITH VARIABLE RESULT ???

  if VAREXISTS('connection_variable_abc')=0 then
      create variable connection_variable_abc integer;
  end if;

   IF connection_variable_abc <> 0 THEN
      SELECT GivenName
      FROM Employees
   ELSE
      SELECT Surname, GivenName
      FROM Employees
   END IF
END;

So I guess when a RESULT SET clause is missing in the SP's definition, the system catalog uses the first "real" SELECT statement (i.e. without an INTO or other "consuming" clauses) within the body to construct the result set information.

[Former Member · Accepted solution]

So, why when I try to select from below procedure I got "returned a result set with different schema than expected"?

CREATE OR REPLACE PROCEDURE Names()
BEGIN
// how to use here DESCRIBE WITH VARIABLE RESULT ???

  if VAREXISTS('connection_variable_abc')=0 then
      create variable connection_variable_abc integer;
  end if;

   IF connection_variable_abc <> 0 THEN
      SELECT GivenName into #temp1
      FROM Employees;
   ELSE
      SELECT Surname, GivenName into #temp1
      FROM Employees;
   END IF;

   select * from #temp1
END;

[Former Member · Accepted solution]

So I assume content of the SYSPROCPARM do not change EVER after procedure was created, I cannot call anything to refresh SYSPROCPARM other than DROP and recreate? And if I do not specify RESULT SET clause, then only solution is to call every time procedure WITH (columns definition).

[VolkerBarth · Accepted solution]

Hm, I'm not in the position to tell whether that is a general restriction or not... (FWIW, I had found a FAQ here dealing with the same problem and option max_plans_cached set to 0 temporarily, but that does not make a difference for me here.)

Note, simply using CALL NamesV2() does work in my tests without having to supply the expected column definition.