116

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.

Improve this question
4
  • Why do you need to qualify the table name with the DB name? I guess a similar question was asked before this one. Commented Jun 1, 2009 at 23:51
  • 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. Commented Jun 2, 2009 at 0:12
  • 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. Commented Jun 2, 2009 at 14:43
  • 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. Commented Apr 17, 2020 at 10:46

8 Answers 8

Reset to default
165

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
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

great answer...you actually ANSWERED the question ASKED rather than giving a work around.
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.
40

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.

Improve this answer

8 Comments

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.
@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.
@jwize: No, you don't need to separate them, that can be done in the same block.
@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.
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.
|
21

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
Improve this answer

2 Comments

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.
Great! This should actually be marked as the real correct answer!
6

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

Improve this answer

Comments

3

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

Improve this answer

Comments

3

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
Improve this answer

Comments

0

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.

Improve this answer

Comments

0

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

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.