Global Variables with GO

Question

I'm trying to batch some SQL scripts. At the top I've declared some variables which I deem global in the sense of the term

So:

DECLARE @someVar1 DECLARE @someVar2 ...etc.  GO  Some batch of SQL here that sets @someVar1 and @SomeVar2 to a value and uses it in the SQL statement  GO   Some batch of SQL here that sets @someVar1 and @SomeVar2 to a value and uses it in the SQL statement  GO ... 

the declarations are out of scope...meaning when I run this, the subsequent batch scripts do not find those declarations. Is there a way to keep them global for all batch scripts that are utilizing or setting these variables for use?

Answer 1

Temporary tables do survive go's:

create table #vars (someVar1 varchar(10), someVar2 int) insert #vars values ('abc',123) 

Get value:

select someVar1 from #vars 

Set value

update #vars set someVar1 = 'def' 

Temporary tables are specific to your connection, so they're not more global than they have to be.

Answer 2

You can declare Scripting Variables, which can be defined explicitly by using the setvar command and don't go out of scope on the GO statement.

e.g.

--Declare the variable :setvar MYDATABASE master --Use the variable USE $(MYDATABASE); SELECT * FROM [dbo].[refresh_indexes] GO --Use again after a GO SELECT * from $(MYDATABASE).[dbo].[refresh_indexes]; GO 

For more info go to: http://technet.microsoft.com/en-us/library/ms188714.aspx