如何使SQL SELECT @variable =表达式赋值像电子表格一样工作？

Disclaimer: This is an "asked-and-answered question" posted in accordance with the FAQ statement that it's "perfectly fine to ask and answer your own programming question". Its purpose is to encourage members of the SQL Anywhere programming community to use * by seeding the "sqlanywhere" tag with some real-world content. Edits are welcome, as are other answers, and it has been marked "community wiki" to facilitate that, as well as to avoid claims of gaming the reputation system.

免责声明：这是根据常见问题解答声明发布的“问答式回答问题”，“完全可以询问并回答您自己的编程问题”。其目的是鼓励SQL Anywhere编程社区的成员通过将“sqlanywhere”标记与一些真实内容一起播种来使用*。欢迎编辑，以及其他答案，并且它已被标记为“社区维基”以促进这一点，并且避免声称游戏声誉系统。

I'm writing a stored procedure for SQL Anywhere 9.0.2, here's some code:

我正在为SQL Anywhere 9.0.2编写存储过程，这里有一些代码：

...
declare
@v_d datetime, @v_d1 datetime, @v_d2 datetime
....
select @v_d1 = @v_d, @v_d2 = dateadd(dd, 1, @v_d1)
...

it turns out that @v_d2 will not be set as expected. if I modify it to:

事实证明，@ v_d2不会按预期设置。如果我将其修改为：

...
declare
@v_d datetime, @v_d1 datetime, @v_d2 datetime
....
select @v_d1 = @v_d
select @v_d2 = dateadd(dd, 1, @v_d1)
...

nothing happens, all the same. finally, I changed it to :

什么都没发生，都一样。最后，我改为：

...
declare
@v_d datetime, @v_d1 datetime, @v_d2 datetime
....
begin
   select @v_d1 = @v_d
end
begin
   select @v_d2 = dateadd(dd, 1, @v_d1)
end
...

now it works fine, looks kind of stupid though. my question is: is it a known issue? or maybe I could do it more neatly? any comments will be appreciated. thanks!

现在它工作正常，看起来有点愚蠢。我的问题是：这是一个众所周知的问题吗？或者我可以更整洁地做到这一点？任何意见将不胜感激。谢谢！

1 个解决方案

#1

You are looking for spreadsheet-style automatic recalculation for host variable assignments in a Transact-SQL SELECT list. I am curious about whether Microsoft SQL Server or Sybase ASE do it, but this is SQL Anywhere...

您正在寻找Transact-SQL SELECT列表中主机变量赋值的电子表格式自动重新计算。我很好奇Microsoft SQL Server或Sybase ASE是否这样做，但这是SQL Anywhere ...

From the behavior of your original code, it looks like references to host variables (the reference to @v_d1 in the call to dateadd) always refer to the values that existed when the statement started (null), not the recalculated value (the value from @v_d):

从原始代码的行为来看，它看起来像对宿主变量的引用（对dateadd的调用中对@v_d1的引用）总是引用语句开始时存在的值（null），而不是重新计算的值（来自的值） @v_d）：

CREATE PROCEDURE p AS
declare
@v_d datetime, @v_d1 datetime, @v_d2 datetime
SELECT @v_d = CURRENT TIMESTAMP
select @v_d1 = @v_d, @v_d2 = dateadd(day, 1, @v_d1)
SELECT @v_d, @v_d1, @v_d2
GO

SELECT * FROM p();

@v_d,@v_d1,@v_d2
'2009-05-08 15:13:43.964','2009-05-08 15:13:43.964',(NULL)

You report that your second version (separate SELECT statements) does not work, but the following works for me:

您报告您的第二个版本（单独的SELECT语句）不起作用，但以下适用于我：

DROP PROCEDURE p;

CREATE PROCEDURE p AS
declare
@v_d datetime, @v_d1 datetime, @v_d2 datetime
SELECT @v_d = CURRENT TIMESTAMP
select @v_d1 = @v_d
select @v_d2 = dateadd(dd, 1, @v_d1)
SELECT @v_d, @v_d1, @v_d2
GO

SELECT * FROM p();

@v_d,@v_d1,@v_d2
'2009-05-08 15:12:54.339','2009-05-08 15:12:54.339','2009-05-09 15:12:54.339'

The spreadsheet-style recalculation you are looking for DOES apply to select list items coded "expression AS identifier" (but that's not exactly what you want)...

您正在寻找的电子表格式重新计算适用于编码“表达式AS标识符”的选择列表项（但这不是您想要的）...

DROP PROCEDURE p;

CREATE PROCEDURE p AS
declare @v_d datetime
SELECT CURRENT TIMESTAMP INTO @v_d
select @v_d AS v_d, dateadd(day, 1, v_d) AS v_d1, dateadd(day, 1, v_d1) AS v_d2
GO

SELECT * FROM p();

v_d,v_d1,v_d2
'2009-05-08 15:14:27.292','2009-05-09 15:14:27.292','2009-05-10 15:14:27.292'

If you want to spreadsheet-style recalculation AND you want host variable assignments, I suggest you switch to the Watcom-SQL dialect so you can use the INTO list:

如果你想进行电子表格式重新计算并且你想要主机变量赋值，我建议你切换到Watcom-SQL方言，这样你就可以使用INTO列表了：

DROP PROCEDURE p;

CREATE PROCEDURE p() BEGIN
   DECLARE @v_d  datetime;
   DECLARE @v_d1 datetime;
   DECLARE @v_d2 datetime;
SET @v_d = CURRENT TIMESTAMP;
SELECT @v_d AS v_d, 
       dateadd(day, 1, v_d) AS v_d1, 
       dateadd(day, 1, v_d1) AS v_d2
  INTO @v_d,
       @v_d1,
       @v_d2;
SELECT @v_d,
       @v_d1,
       @v_d2;
END;

SELECT * FROM p();

@v_d,@v_d1,@v_d2
'2009-05-08 15:27:09.808','2009-05-09 15:27:09.808','2009-05-10 15:27:09.808'

The behavior is the same in SQL Anywhere versions 9.0.2 and 11.0.1.

SQL Anywhere版本9.0.2和11.0.1中的行为相同。

Breck

布瑞克

#1