Is there a way to persist a variable across a go?

I prefer the this answer from this question Global Variables with GO

Which has the added benefit of being able to do what you originally wanted to do as well.

The caveat is that you need to turn on SQLCMD mode (under Query->SQLCMD) or turn it on by default for all query windows (Tools->Options then Query Results->By Default, open new queries in SQLCMD mode)

Then you can use the following type of code (completely ripped off from that same answer by Oscar E. Fraxedas Tormo)

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

If you are using SQL Server you can setup global variables for entire scripts like:

:setvar sourceDB "lalalallalal"

and use later in script as:

$(sourceDB)

Make sure SQLCMD mode is on in Server Managment Studi, you can do that via top menu Click Query and toggle SQLCMD Mode on.

More on topic can be found here: MS Documentation


The go command is used to split code into separate batches. If that is exactly what you want to do, then you should use it, but it means that the batches are actually separate, and you can't share variables between them.

In your case the solution is simple; you can just remove the go statements, they are not needed in that code.

Side note: You can't use a variable in a use statement, it has to be the name of a database.


Use a temporary table:

CREATE TABLE #variables
(
    VarName VARCHAR(20) PRIMARY KEY,
    Value VARCHAR(255)
)
GO

Insert into #variables Select 'Bob', 'SweetDB'
GO

Select Value From #variables Where VarName = 'Bob'
GO

DROP TABLE #variables
go