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
8061114
accept rate: 36%

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:

×91
×41
×23
×5

question asked: 02 Sep '14, 13:54

question was seen: 4,754 times

last updated: 03 Sep '14, 11:07