Question Re: Significant difference between SQL/Anywhere and MS SQL/Server - CURSOR variable evaluation in Technology Q&A

Significant difference between SQL/Anywhere and MS SQL/Server - CURSOR variable evaluation

Former Member — Tue, 02 Sep 2014 13:54:48 GMT

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

Re: Significant difference between SQL/Anywhere and MS SQL/Server - CURSOR variable evaluation

Former Member — Tue, 02 Sep 2014 15:34:15 GMT

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.

Re: Significant difference between SQL/Anywhere and MS SQL/Server - CURSOR variable evaluation

Former Member — Tue, 02 Sep 2014 16:23:52 GMT

Elmi, thanks for your comment,

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?)
Does this conform to SQL/2008 or is the standard too vague on the issue?

Re: Significant difference between SQL/Anywhere and MS SQL/Server - CURSOR variable evaluation

Breck_Carter — Tue, 02 Sep 2014 20:58:51 GMT

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.

Re: Significant difference between SQL/Anywhere and MS SQL/Server - CURSOR variable evaluation

Former Member — Wed, 03 Sep 2014 11:07:37 GMT

As far as I can tell, there is no description of the behaviour in the SQL Anywhere documentation.
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.