在 IF EXISTS 子句中分配变量 [英] Assigning a variable inside an IF EXISTS clause

问题描述

尝试在 TSQL 的 if exists 子句中分配变量

Trying to assign a variable inside an if exists clause for TSQL

DECLARE @myvar  int

IF EXISTS (SELECT @myvar = theTable.varIWant..... )

我认为这会奏效，但显然不行?或者(更有可能)我做错了.

I thought this would work, but apparently not? Or perhaps (more likely) I'm doing it wrong.

推荐答案

在我安装的 SQL Server 2008 R2 中，它根本无法编译.解析器抱怨 = 附近的语法不正确.

In my installation of SQL Server 2008 R2, it simply doesn't compile. The parser complains about there being incorrect syntax near =.

我相信它一定与在单个 SELECT 语句中混合赋值和数据检索有关，这在 SQL Server 中是不允许的:您可以有一个或另一个.因为，当您分配值时，不会返回行集，但 EXISTS 谓词期望它是，因此在该上下文中不允许进行分配，因此，为了避免混淆，也许限制必须具有被明确强加.

I believe it must have something to do with mixing value assignment and data retrieval in a single SELECT statement, which is not allowed in SQL Server: you can have either one or the other. Since, when you assign values, the row set is not returned but the EXISTS predicate expects it to be, the assignment cannot be allowed in that context, so, to avoid confusion, perhaps, the limitation must have been imposed explicitly.

您在评论中谈到的解决方法是一个不错的方法，但是当变量在赋值之前已经获得一个值时，它可能无法在批处理中间的某个地方很好地工作.所以我可能会改用这个解决方法:

Your workaround, which you are talking about in a comment, is a decent one, but might not work well somewhere in the middle of a batch when the variable has already got a value before the assignment. So I would probably use this workaround instead:

SELECT @myvar = ...
IF @@ROWCOUNT > 0 ...

根据 MSDN，@@ROWCOUNT 系统函数返回查询读取的行数.

As per MSDN, the @@ROWCOUNT system function returns the number of rows read by the query.

这篇关于在 IF EXISTS 子句中分配变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！