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;

asked 14 Aug, 05:16

BlueMark's gravatar image

BlueMark
246111624
accept rate: 50%

Comment Text Removed

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

answered 14 Aug, 06:16

Volker%20Barth's gravatar image

Volker Barth
31.5k318461676
accept rate: 33%

converted 14 Aug, 06:43

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.

(14 Aug, 06:47) BlueMark
Replies hidden

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

(14 Aug, 07:27) BlueMark
Replies hidden

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

(14 Aug, 07:50) Volker Barth

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.

(14 Aug, 08:00) Volker Barth

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;
(14 Aug, 09:08) BlueMark

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

(14 Aug, 09:47) BlueMark

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.

(14 Aug, 11:09) Volker Barth
showing 2 of 7 show all flat view
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:

×32
×25
×17
×5

question asked: 14 Aug, 05:16

question was seen: 153 times

last updated: 14 Aug, 11:09