|
I need to create procedure without a I know I can use 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;
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)); 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 '17, 06:47)
BlueMark
Replies hidden
So for what purpose is the WITH VARIABLE RESULT clause and where we use it?
(14 Aug '17, 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 '17, 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 '17, 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 '17, 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 '17, 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 '17, 11:09)
Volker Barth
|
