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?
|
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. Elmi, thanks for your comment,
(02 Sep '14, 16:23)
DougMWat
Replies hidden
(03 Sep '14, 11:07)
Elmi Eflov
|
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.