I have following code in a procedure, it works fine in the Sybase ASA 6, but after migrating it to the 12.5 version, it gives error.

Here's the basic example of the code:

while(@@sqlstatus = 0) 
begin 
  declare crs_sid dynamic scroll cursor for 
  select s_th from dba.table where s_var in(1,2,3) open crs_sid with hold
  fetch next crs_sid into @sid
  while(@@sqlstatus = 0) 
  begin
    ..
    fetch next crs_sid into @sid 
  end
  close crs_sid
end

Here's the error I get:

Item crs_sid already exists.

asked 10 Sep '13, 04:24

sam's gravatar image

sam
315141624
accept rate: 0%

edited 10 Sep '13, 04:45

Reimer%20Pods's gravatar image

Reimer Pods
4.2k344583


AFAIK, cursor names must be unique in current SA releases - I don't know whether that was different in very old days... (BTW, you're not using version 12.5, as that would be an ASE version, right?)

Confine this discussion (though it's based on FOR loops - but they do use cursor names, too):

Is there any purpose to the FOR for-loop-name and cursor-name fields?

There you'll find the definite answer by Ivan, as documented in DCX for the DECLARE CURSOR statement:

When a single statement is processed, all of the DECLARE CURSOR statements must use distinct names (even if the cursors are declared in scopes that do not overlap). [...]

permanent link

answered 10 Sep '13, 05:44

Volker%20Barth's gravatar image

Volker Barth
31.3k312458674
accept rate: 33%

edited 10 Sep '13, 05:48

Thanks for the answer. Sorry I meant SQL Anywhere 12.

(10 Sep '13, 06:04) sam

Ok it seems that the problem was solved only by deallocating the cursor after closing it.

(10 Sep '13, 07:10) sam
Replies hidden

That seems odd: The SA 12 docs tell:

DEALLOCATE statement
This statement has no effect in SQL Anywhere, and is ignored. It is provided for compatibility with Adaptive Server Enterprise and Microsoft SQL Server. Refer to your Adaptive Server Enterprise or Microsoft SQL Server documentation for more information about this statement.

(10 Sep '13, 08:02) Volker Barth
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:

×106
×45
×18

question asked: 10 Sep '13, 04:24

question was seen: 1,038 times

last updated: 10 Sep '13, 08:02