"CREATE VARIABLE" statement within a stored procedure: error

SQL Anywhere Network Server Version 12.0.1.3967

I use the example from the documentation on CREATE VARIABLE statement:
"This example creates a variable named v1 and sets its value to 10, regardless of whether or not the v1 variable already exists.
CREATE OR REPLACE VARIABLE v1 INT = 10;"

1)
create procedure "dba"."CREATE_VARIABLE_statement"()
begin
CREATE OR REPLACE VARIABLE v1 INT = 10;
end
;
call CREATE_VARIABLE_statement();
select v1;

Result = NULL

But:
2a)
create procedure "dba"."CREATE_VARIABLE_statement"()
begin
execute immediate 'CREATE OR REPLACE VARIABLE v1 INT = 10';
end
;
call CREATE_VARIABLE_statement();
select v1;

Result = 10

2b)
create procedure "dba"."CREATE_VARIABLE_statement"()
begin
CREATE OR REPLACE VARIABLE v1 INT;
set v1 = 10;
end
;
call CREATE_VARIABLE_statement();
select v1;

Result = 10

sa-12

asked 09 Dec '13, 04:26

Ilia63
1.2k●51●57●82
accept rate: 44%

flat view

One Answer:

active answers oldest answers newest answers popular answers

This appears to be a bug. I have reproduced it on both the latest v12 and v16. I had a quick look a the code and it seems correct... so there is obviously something strange going on that will require more investigation.

Until this issue is fixed you can use 2b as a work-around.

(I'll post back here when the issue is fixed)

permanent link

answered 09 Dec '13, 10:08

Mark Culp
24.9k●10●141●297
accept rate: 41%

FYI: This issue is begin tracked as Engineering Issue (QTS) #693422

(09 Dec '13, 10:19) Mark Culp

This has been fixed in 12.0.1 build 4031 and 16.0.0 build 1758.

(09 Dec '13, 14:25) Mark Culp

flat view

Your answer

toggle preview

community wiki:

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

learn more about Markdown

Question tags:

sa-12 ×438

question asked: 09 Dec '13, 04:26

question was seen: 1,969 times

last updated: 09 Dec '13, 14:25

SAP SQL Anywhere

"CREATE VARIABLE" statement within a stored procedure: error

Follow this question

Related questions