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

Answer 3

There's no such thing as a global variable, but you could do something like this: Link

Usage example:

EXEC sp_SetGlobalVariableValue 'Test1', 'test1'
EXEC sp_SetGlobalVariableValue 'Test2', 'test2'

GO

EXEC sp_GetGlobalVariableValue 'Test1'
EXEC sp_GetGlobalVariableValue 'Test2'

GO

EXEC sp_GetGlobalVariableValue 'Test1'
EXEC sp_GetGlobalVariableValue 'Test2'

GO

Using the stored procedures mentioned in the link above, I was able to set global variables and have them last between GO's.

Answer 4

You may not need use batches, in which case you can get your variables to survive for a very long time. I'm not sure if this is best practice, but for example a function declaration would usually have to be the only statement in a batch like so:

Declare @batch_scope_var INT
GO --This is required

CREATE FUNCTION dbo.Half(@function_scope_var int)
RETURNS int
WITH EXECUTE AS CALLER
AS
BEGIN
    RETURN @function_scope_var/2
END
GO --This is required

SET @batch_scope_var = 4 --Bugger, this is now out of scope and won't execute!
print dbo.Half(@batch_scope_var)
GO

However you can re-write this using *sp_executesql* and avoid all those batches like so:

Declare @batch_scope_var INT

execute dbo.sp_executesql @statement = N'
CREATE FUNCTION dbo.Half(@function_scope_var int)
RETURNS int
WITH EXECUTE AS CALLER
AS
BEGIN
    RETURN @function_scope_var/2
END
'

SET @batch_scope_var = 4
print dbo.Half(@batch_scope_var)
GO

It really depends on the complexity of your script as to whether you want to do this as it can get a bit messy and you lose you syntax highlighting as soon as everything is in a giant string :)

Answer 5

The variables are going out of scope because the GO statement signals the end of the batch. The Microsoft documentation topic Transact-SQL Variables has this example:

The scope of a variable lasts from the point it is declared until the end of the batch or stored procedure in which it is declared. For example, the following script generates a syntax error because the variable is declared in one batch and referenced in another:

USE AdventureWorks2008R2;
GO
DECLARE @MyVariable int;
SET @MyVariable = 1;
-- Terminate the batch by using the GO keyword.
GO 
-- @MyVariable has gone out of scope and no longer exists.

-- This SELECT statement generates a syntax error because it is
-- no longer legal to reference @MyVariable.
SELECT BusinessEntityID, NationalIDNumber, JobTitle
FROM HumanResources.Employee
WHERE BusinessEntityID = @MyVariable;

I think what you want to do is create a temporary table to store your "global" variables. See Andomar's answer for an example of how to do that. He's got one variable per row. I've also see temp tables that having a column for every global variable wanted and just single row is inserted into the table. This allows for each variable to have a different data type.