How do I get SQL SELECT @variable = expression assignments to work like a spreadsheet?

Question

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 StackOverflow 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.

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

...
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:

...
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!

Answer 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...

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):

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:

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)...

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:

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.

Breck