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

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

If you try to do the following you will get an error, because the server does not understand the command 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.

Answer 4

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

Answer 5

you have to redefine @Username after GO statement