The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

This may be a documentation or standards concern, but the function below demonstrates very different results between SQL/Anywhere (12.0.0) and MS SQL/Server (2008 R2). On SQL/Anywhere, this function always returns 2, but on SQL/Server, it returns the value of the parameter passed in. In other words, the return value hinges on when @parm1 is evaluated: for SQL/Anywhere, it's at the OPEN, for SQL/Server, it's at the DECLARE. Microsoft documents this as "Variables may be used as part of the select_statement that declares a cursor. Cursor variable values do not change after a cursor is declared. In SQL Server version 6.5 and earlier, variable values are refreshed every time a cursor is reopened." I didn't see this mentioned in the SQL/Anywhere documentation, though I didn't look too hard - is it an SQL/92 vs SQL/2008 standards discrepancy?

ALTER FUNCTION fncursorTest( @parm1 INTEGER )
RETURNS INTEGER
AS
BEGIN
    DECLARE @retval INTEGER;
    DECLARE cursor1 CURSOR FOR
        SELECT @parm1;
    SET @parm1 = 2;
    OPEN cursor1;
    FETCH cursor1 INTO @retval;
    CLOSE cursor1;
    DEALLOCATE cursor1;
    RETURN @retval;
END

asked 02 Sep '14, 13:54

DougMWat's gravatar image

DougMWat
1365512
accept rate: 0%

The term "cursor variable" has a meaning quite distinct from "parameter" which is what @parm1 is. See the section "Using the cursor Variable" in Transact-SQL Cursors.

(02 Sep '14, 20:58) Breck Carter

In SQL Anywhere, a variable used in a query is evaluated no earlier than at the cursor OPEN time, and possibly as late as FETCH time.

permanent link

answered 02 Sep '14, 15:34

Elmi%20Eflov's gravatar image

Elmi Eflov
7811014
accept rate: 31%

Elmi, thanks for your comment,

  1. Do you know if this is described in the SQL Anywhere documentation somewhere (it's not mentioned in the "SQL dialects and compatibility" / "SQL Anywhere features that differ from other SQL implementations" section. Is this compatible with Adaptive Server Enterprise?)
  2. Does this conform to SQL/2008 or is the standard too vague on the issue?
(02 Sep '14, 16:23) DougMWat
Replies hidden
  1. As far as I can tell, there is no description of the behaviour in the SQL Anywhere documentation.
  2. SQL/2011 standard, Foundation, Section 15.1, General Rules 5)a )i) implies that all variables used in a cursor are evaluated at OPEN time.
(03 Sep '14, 11:07) Elmi Eflov
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:

×78
×28
×16
×5

question asked: 02 Sep '14, 13:54

question was seen: 2,828 times

last updated: 03 Sep '14, 11:07