How to combine variable assignment with data-retrieval operations in T-SQL

Question

Just to clarify, I'm running Sybase 12.5.3, but I am lead to believe that this holds true for SQL Server 2005 too. Basically, I'm trying to write a query that looks a little like this, I've simplified it as much as possible to highlight the problem:

DECLARE @a int,  @b int, @c int

SELECT
     @a = huzzah.a
    ,@b = huzzah.b
    ,@c = huzzah.c
FROM (
    SELECT
         1 a
        ,2 b
        ,3 c
) huzzah

This query gives me the following error: "Error:141 A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations."

The only work around that I've got for this so far, is to insert the derived-table data into a temporary table and then select it right back out again. Which works fine, but the fact that this doesn't work irks me. Is there a better way to do this?

Answer 1

I've just ran your code against 12.5.3 and it parses fine...doesn't return anything but it does run. Have you maybe simplified the problem a bit too much because I'm not seeing any error messages at all.

Just to be clear, the following runs and returns what you'd expect.

DECLARE @a int,  @b int, @c int

SELECT
     @a = huzzah.a
    ,@b = huzzah.b
    ,@c = huzzah.c
FROM (
    SELECT
         1 a
        ,2 b
        ,3 c
) huzzah

select @a
select @b
select @c

Answer 2

The error does appear as described in 12.5.3 esd 4 & 7, it runs fine in 12.5.4 esd 4 & 6.

Looks like a bug that's been patched, your only options seem to be workaround or patch.

Have found what appears to be the bug 377625