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

Question

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

Declare @bob as varchar(50);
Set @bob = 'SweetDB'; 
GO
USE @bob  --- see note below
GO
INSERT INTO @bob.[dbo].[ProjectVersion] ([DB_Name], [Script]) VALUES (@bob,'1.2')

See this SO question for the 'USE @bob' line.

Chant

Why do you need to qualify the table name with the DB name? I guess a similar question was asked before this one. – Besiege

And there's no way to qualify the table names with the database name in a variable like that. With his previous question about using a variable with the USE statement, I'm guessing he's going to need to do everything in dynamic SQL, with all the pain that drags to the table. – Tetrameter

The actual script integrates 4 different databases. I have commented instructions to find and replace dbName1, dbName2, dbName3, and dbName4. I just thought it would be less error prone for the client to just set four variables. – Chant

The question title is a really important question, but the example code is terrible. As the accepted answer shows, you didn't need 'go' in your example. The result is that the accepted answer doesn't answer the question in your title. – Tether

Answer 1

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.

Badinage

Some SQL Statements must be the first statement in a block (the region between GO statements). For example: CREATE PROCEDURE or CREATE FUNCTION must both occur before any other statements - either at the top of the script or immediately following the GO statement (note: white space and comments are allowed before these statements). When running scripts where such statements must occur after other logic, the GO statements are required. But I must agree that in most cases, the GO statements can be removed. – Pickup

@Zarepheth: Good point. It's not needed in this specific code, but it's useful to know that they might be needed in some cases. – Badinage

Why the downvote? If you don't explain what it is that you think is wrong, it can't improve the answer. – Badinage

How about when your insert rows in one chuck and then join them in the following, don't you need a go statement between? – Sanchez

@jwize: No, you don't need to separate them, that can be done in the same block. – Badinage

This guy suggests using GO so when you say "they are not needed in your code", this kind of puts me back to square 1. – Bueschel

@Ben: The go command is used to split code into separate batches. If that is 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. – Badinage

Guffa, your last comment is a much better answer than the one you originally gave. Perhaps you might consider editing your answer to include this? I appreciate that your comment "they are not needed in your code" is an implicit direct answer to the OP but your last comment is much more broadly useful. – Bueschel

I downvoted this answer, because it does not answer the question. There are situations where you NEED to split your query with GO statements, and still you want to use a kind of global variable. – Bluebeard

Answer 2

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

Jenine

great answer...you actually ANSWERED the question ASKED rather than giving a work around. – Ustkamenogorsk

This is the right answer. Nice solution. It's additionally nice that, if you use a large number of variables, they're all in one easily accessible table, no scrolling up and down the SP looking for your declarations. – Brynnbrynna

Answer 3

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

Advisedly

I was redirecting query output to another file (:out filename) in SQLCMD mode, and to get the output flushed to the file you have to execute a GO, so this :setvar syntax is necessary to replace normal variables in that situation since you're forced to split things up into batches. – Tisman

Great! This should actually be marked as the real correct answer! – Bluebeard

Answer 4

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

Outdistance

Answer 5

Temp tables are retained over GO statements, so...

SELECT 'value1' as variable1, 'mydatabasename' as DbName INTO #TMP

-- get a variable from the temp table
DECLARE @dbName VARCHAR(10) = (select top 1 #TMP.DbName from #TMP)
EXEC ('USE ' + @dbName)
GO

-- get another variable from the temp table
DECLARE @value1 VARCHAR(10) = (select top 1 #TMP.variable1 from #TMP)

DROP TABLE #TMP

It's not pretty, but it works

Swor

Answer 6

Create your own stored procedures which save/load to a temporary table.

MyVariableSave   -- Saves variable to temporary table. 
MyVariableLoad   -- Loads variable from temporary table.

Then you can use this:

print('Test stored procedures for load/save of variables across GO statements:')

declare @MyVariable int = 42
exec dbo.MyVariableSave @Name = 'test', @Value=@MyVariable
print('  - Set @MyVariable = ' + CAST(@MyVariable AS VARCHAR(100)))

print('  - GO statement resets all variables')
GO -- This resets all variables including @MyVariable

declare @MyVariable int
exec dbo.MyVariableLoad 'test', @MyVariable output
print('  - Get @MyVariable = ' + CAST(@MyVariable AS VARCHAR(100)))

Output:

Test stored procedures for load/save of variables across GO statements:
  - Set @MyVariable = 42
  - GO statement resets all variables
  - Get @MyVariable = 42

You can also use these:

exec dbo.MyVariableList       -- Lists all variables in the temporary table.
exec dbo.MyVariableDeleteAll  -- Deletes all variables in the temporary table.

Output of exec dbo.MyVariableList:

Name    Value
test    42

It turns out that being able to list all of the variables in a table is actually quite useful. So even if you do not load a variable later, its great for debugging purposes to see everything in one place.

This uses a temporary table with a ## prefix, so it's just enough to survive a GO statement. It is intended to be used within a single script.

And the stored procedures:

-- Stored procedure to save a variable to a temp table.
CREATE OR ALTER PROCEDURE MyVariableSave 
    @Name varchar(255),
    @Value varchar(MAX)
WITH EXECUTE AS CALLER
AS  
BEGIN
    SET NOCOUNT ON
    IF NOT EXISTS (select TOP 1 * from tempdb.sys.objects where name = '##VariableLoadSave')
    BEGIN
        DROP TABLE IF EXISTS ##VariableLoadSave
        CREATE TABLE ##VariableLoadSave
        (
            Name varchar(255),
            Value varchar(MAX)
        )
    END
    UPDATE ##VariableLoadSave SET Value=@Value WHERE Name=@Name
    IF @@ROWCOUNT = 0
        INSERT INTO ##VariableLoadSave SELECT @Name, @Value
END
GO
-- Stored procedure to load a variable from a temp table.
CREATE OR ALTER PROCEDURE MyVariableLoad 
    @Name varchar(255),
    @Value varchar(MAX) OUT
WITH EXECUTE AS CALLER
AS  
BEGIN
    IF EXISTS (select TOP 1 * from tempdb.sys.objects where name = '##VariableLoadSave')
    BEGIN
        IF NOT EXISTS(SELECT TOP 1 * FROM ##VariableLoadSave WHERE Name=@Name)
        BEGIN
            declare @ErrorMessage1 as varchar(200) = 'Error: cannot find saved variable to load: ' + @Name
            raiserror(@ErrorMessage1, 20, -1) with log
        END

        SELECT @Value=CAST(Value AS varchar(MAX)) FROM ##VariableLoadSave
        WHERE Name=@Name
    END
    ELSE
    BEGIN
        declare @ErrorMessage2 as varchar(200) = 'Error: cannot find saved variable to load: ' + @Name
        raiserror(@ErrorMessage2, 20, -1) with log
    END
END
GO
-- Stored procedure to list all saved variables.
CREATE OR ALTER PROCEDURE MyVariableList
WITH EXECUTE AS CALLER
AS  
BEGIN
    IF EXISTS (select TOP 1 * from tempdb.sys.objects where name = '##VariableLoadSave')
    BEGIN
        SELECT * FROM ##VariableLoadSave
        ORDER BY Name
    END
END
GO
-- Stored procedure to delete all saved variables.
CREATE OR ALTER PROCEDURE MyVariableDeleteAll
WITH EXECUTE AS CALLER
AS  
BEGIN
    DROP TABLE IF EXISTS ##VariableLoadSave
    CREATE TABLE ##VariableLoadSave
    (
        Name varchar(255),
        Value varchar(MAX)
    )
END

Falter

Answer 7

If you just need a binary yes/no (like if a column exists) then you can use SET NOEXEC ON to disable execution of statements. SET NOEXEC ON works across GO (across batches). But remember to turn EXEC back on with SET NOEXEC OFF at the end of the script.

IF COL_LENGTH('StuffTable', 'EnableGA') IS NOT NULL
    SET NOEXEC ON -- script will not do anything when column already exists

ALTER TABLE dbo.StuffTable ADD EnableGA BIT NOT NULL CONSTRAINT DF_StuffTable_EnableGA DEFAULT(0)
ALTER TABLE dbo.StuffTable SET (LOCK_ESCALATION = TABLE)
GO
UPDATE dbo.StuffTable SET EnableGA = 1 WHERE StuffUrl IS NOT NULL
GO
SET NOEXEC OFF

This compiles statements but does not execute them. So you'll still get "compile errors" if you reference schema that doesn't exist. So it works to "turn off" the script 2nd run (what I'm doing), but does not work to turn off parts of the script on 1st run, because you'll still get compile errors if referencing columns or tables that don't exist yet.

Tap

Answer 8

You can make use of NOEXEC follow he steps below:

Create table

#temp_procedure_version(procedure_version varchar(5),pointer varchar(20))

insert procedure versions and pointer to the version into a temp table #temp_procedure_version

--example procedure_version pointer

insert into temp_procedure_version values(1.0,'first version')

insert into temp_procedure_version values(2.0,'final version')

then retrieve the procedure version, you can use where condition as in the following statement

Select @ProcedureVersion=ProcedureVersion from #temp_procedure_version where pointer='first version'

IF (@ProcedureVersion='1.0')
    BEGIN
    SET NOEXEC OFF  --code execution on 
    END
ELSE
    BEGIN 
    SET NOEXEC ON  --code execution off
    END 

--insert procedure version 1.0 here

Create procedure version 1.0 as.....

SET NOEXEC OFF -- execution is ON

Select @ProcedureVersion=ProcedureVersion from #temp_procedure_version where pointer='final version'

IF (@ProcedureVersion='2.0')
    BEGIN
    SET NOEXEC OFF  --code execution on 
    END
ELSE
    BEGIN 
    SET NOEXEC ON  --code execution off
    END 

Create procedure version 2.0 as.....

SET NOEXEC OFF -- execution is ON

--drop the temp table

Drop table #temp_procedure_version

Edveh