不理解错误消息:必须声明标量变量“@Username”。

I have a simple script updating and showing a useraccount. (working with the management studio 2010) For preventing user errors I wanna use a variable in SQL (never did this before).

我有一个简单的脚本更新和显示一个useraccount。(与management studio 2010一起工作)为了防止用户错误，我想在SQL中使用一个变量(以前从来没有这样做过)。

When reading tutorials it should be as simple as codesample below except I i'm getting an error message. Searching the web for people with the same error, I end up seeing very complex code with the same error. Can someone give me a clue.

当阅读教程时，它应该像下面的codesample一样简单，除非我得到一条错误消息。在web上搜索有相同错误的人，我最终会看到非常复杂的代码也有相同的错误。谁能给我一点线索吗?

DECLARE @Username nvarchar(256) 
Set @Username = 'theUsername'

UPDATE aspnet_Membership
SET IsLockedOut = 0
WHERE UserId IN (SELECT U.UserId
FROM aspnet_Users as U inner join aspnet_Membership M on U.UserId = M.UserId
WHERE u.UserName = @Username)
GO 
SELECT U.UserId, U.UserName, M.Password, M.IsLockedOut, U.LastActivityDate
FROM aspnet_Users as U inner join aspnet_Membership M on U.UserId = M.UserId
WHERE u.UserName = @Username

Msg 137, Level 15, State 2, Line 3 Must declare the scalar variable "@Username".

Msg 137，第15层，状态2，第3行必须声明标量变量“@Username”。

5 个解决方案

#1

The scope of variable in Transact-SQL is limited by batch. Your script contains two batches separated by "GO"

Transact-SQL中变量的范围是受批限制的。您的脚本包含由“GO”分隔的两个批

#2

There is a GO inside your script, GO divides your script into two batches so have to re-define all used variables after GO, because the scope is limited to this batch.

在脚本中有一个GO, GO将脚本分为两个批，因此必须在GO之后重新定义所有使用的变量，因为范围仅限于此批。

BTW: I don't think, that this GO is necessary, isn't it?

顺便说一句:我不认为这是必要的，不是吗?

Thanks to @gbn and @alexm giving hint, that GO separate statements into batches and not into transactions, see also http://msdn.microsoft.com/en-us/library/ms188037.aspx

感谢@gbn和@alexm给出的提示，将语句分成多个批次，而不是进行交易，参见http://msdn.microsoft.com/en-us/library/ms188037.aspx。

#3

GO separates batches and is a client instruction, not a server one. So the server receives the second batch separately as a new query and in this the variable is not declared.

GO是一个客户端指令，而不是服务器指令。因此，服务器作为一个新查询单独接收第二批处理，在此变量未声明。

If you try to do the following you will get an error, because the server does not understand the command GO:

如果您尝试执行以下操作，您将会得到一个错误，因为服务器不理解命令GO:

DECLARE @SQL varchar(1000);
SET @SQL = 'PRINT ''hello'';
GO
PRINT ''goodbye'';';

The server won't split this into two batches, because batches are things submitted to the server by the client.

服务器不会将其分成两批，因为批是客户端提交给服务器的东西。

#4

Once you write GO, @Username is not available to SELECT query.

一旦写入GO， @Username不能用于选择query。

#5

you have to redefine @Username after GO statement

你必须在GO语句之后重新定义@Username。

#1