如何在T-SQL中将变量赋值与数据检索操作相结合

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:

为了澄清,我正在运行Sybase 12.5.3,但我也相信这也适用于SQL Server 2005。基本上,我正在尝试编写一个看起来有点像这样的查询,我已尽可能地简化它以突出显示问题:

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

此查询给出了以下错误:“错误:141为变量赋值的SELECT语句不能与数据检索操作结合使用。”

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?

到目前为止,我唯一能解决的问题是将派生表数据插入临时表中,然后再将其选中。哪个工作正常,但这不起作用的事实让我感到烦恼。有一个更好的方法吗?

2 个解决方案

#1

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

该错误确实如12.5.3 esd 4和7中所述,它在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

找到了似乎是错误的377625

#2

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.

我刚刚针对12.5.3运行了你的代码并且它解析得很好......除了它确实运行之外没有返回任何东西。您是否可能将问题简化得太多,因为我根本没有看到任何错误消息。

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

#1