Declare global variables for a batch of execution statements - sql server 2005

Question

hi,

i have an SQL statement wherein i am trying to update the table on the client's machine. the sql statement is as follows:

    BEGIN TRANSACTION


    DECLARE @CreatedBy INT

    SELECT  @CreatedBy = [User_Id]
    FROM    Users
    WHERE   UserName = 'Administrator'

    --////////////////////////////////////////////////////////////////////
    --////////////////////////////////////////////////////////////////////

    PRINT @CreatedBy --(Works fine here and shows me the output)

    PRINT N'Rebuilding [dbo].[Some_Master]'
    ALTER TABLE [dbo].[Some_Master]
    ADD [CreatedBy] [BIGINT] NULL,
        [Reason] [VARCHAR](200) NULL
    GO

    PRINT @CreatedBy --(does not work here and throws me an error)


    PRINT N'Updating data in [Some_Master] table'
    UPDATE  Some_Master
    SET     CreatedBy = @CreatedBy

    COMMIT TRANSACTION

but i am getting the following error:

Must declare the scalar variable "@CreatedBy".

Now i have observed if i write the Print statement above the alter command it works fine and shows me its value, but if i try to print the value after the Alter command it throws me the error i specified above.

I dont know why ?? please help!

Thank you

Answer 1

I think you need to remove the GO statement.

Answer 2

It's because of the GO, which signals the end of a batch of commands. So after the GO, it is a separate batch whereby the variable @CreatedBy is no longer in scope.

Try removing the GO statements.

Answer 3

Remove the "GO" statement.