Don't understand error message: Must declare the scalar variable "@Username".

Question

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

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.

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

Answer 1

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

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

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