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

sql

sqlcmd

stored-procedures

dynamic-sql

byAlex Kataev·Dec 19, 2024

Use SQL Server's temporary tables to maintain a variable value past the GO boundary. Here's how you'd do it:

-- Create an icebox for the variable (temporary table)
CREATE TABLE #TempVar(Value INT);

-- Put the variable in the icebox for safekeeping
INSERT INTO #TempVar VALUES (42);  -- Hitchhiker's Guide to the Galaxy, anyone? 

GO

-- Retrieve the not-frozen variable and use it
SELECT @PersistedVar = Value FROM #TempVar;
PRINT @PersistedVar;  -- "42", the answer to everything

-- Don't forget to turn off the fridge (remove the temporary table)
DROP TABLE #TempVar;

Through this method, variables persist beyond GO, countering the limited scope of @variable.

Deconstructing "GO"-ing beyond

Spot unnecessary "GO" commands and remove them

A method to consider before introducing workarounds is reviewing your script to eliminate redundant GOs. A simple restructure can sometimes help maintain the variable's scope.

Variables and stored procedures - A friendship story

When playing with stored procedures or user-defined functions, pass variables as parameters. Thus, they persist through GO via provided parameters:

-- Who's the friend who's always there for a stored procedure? A variable!
EXEC MyStoredProcedure @MyParameter = @PersistedVar;

SQLCMD and variable - The odd couple

While running scripts in SQLCMD mode, you can use :setvar to declare variables. These can then be accessed via $(VARIABLE_NAME), skirting GO:

:setvar MyVariable 42
GO
PRINT '$(MyVariable)'  -- "42", again?! Are we in a loop?

One caveat - dynamically switching databases with USE @variable is off the table—you'll need to go the literal database name route.

SQL's unique quirks and practices

SQL Server has its own peculiarities. The GO statement is your batch terminator, meaning a SQL Server variable has a batch-limited scope. Nevertheless, sometimes you're required to have variables persist beyond one batch.

Workarounds for syntax hitches

Directly using USE @bob doesn't exactly fly; however, for such cases, we construct dynamic SQL as a workaround:

DECLARE @DatabaseName NVARCHAR(128) = N'SampleDB';
DECLARE @DynamicSQL NVARCHAR(MAX) = N'USE ' + QUOTENAME(@DatabaseName) + '; SELECT 1 as TestData;';

-- Knock, knock. Who's there? Que—Que who? Queue-ery apparently. 
EXEC sp_executesql @DynamicSQL;