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?
asked 02 Sep '14, 13:54
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.
answered 02 Sep '14, 15:34