Error: “Must declare the scalar variable” for inse

问题:

I created a SQL script to add an entry in different database. However, when I run the script through SQL Server Management Studio.

declare @address varchar(50)
set @address = 'Hope'

use DB1
go

insert into Address
 values (@address)
go

use DB2
go

insert into Address
 values (@address)
go

I got the following error:

Must declare the scalar variable '@address'

At this point, I'm totally confused because I've declared the variable @address before executing the insert statement. Is it because I'm traversing different database?

As of now, I've just put the actual value in the insert statement just for the sake of completing the task, though I wondered what caused the error.

回答1:

the variable @address only lives in the batch that its defined in, batches are delimited by the the go statement, where it goes out of scope.

try this:

declare @address varchar(50)
set @address = 'Hope'

insert into DB1.dbo.Address
 values (@address)

insert into DB2.dbo.Address
 values (@address)
go

回答2:

It's the GO statement.

all local variable declarations must be grouped in a single batch. This is done by not having a GO command until after the last statement that references the variable. (from MSDN)

回答3:

It's because you are using go between the statement that declares the variable and the statement that uses it.

The go command is not an SQL command, it's a separator between sessions in Management Studio. Just remove all the go commands in your query, and you can use the variable all the way.