Is there a method to keep a variable constant during a game?

Problem

Is there a method to keep a variable constant during a game?

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

For the ‘USE @bob’ line, see this SO query.

Asked by NitroxDM

Solution #1

Make use of a makeshift 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

Answered by RBarryYoung

Solution #2

To split code into distinct batches, use the go command. If that’s exactly what you want, go ahead and use it; however, it means the batches will be independent, and you won’t be able to share variables between them.

The remedy is straightforward in your situation; simply eliminate the go statements; they aren’t required in that function.

Aside: In a use statement, you can’t use a variable; it needs to be the name of a database.

Answered by Guffa

Solution #3

I prefer this response to this question. GO’s Global Variables

Which has the extra benefit of allowing you to do what you wanted to do in the first place.

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 code that follows (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

Answered by Matt Vukomanovic

Solution #4

If you use SQL Server, you may create global variables for entire scripts, such as:

:setvar sourceDB "lalalallalal"

and then use as follows in the script:

$(sourceDB)

Make sure SQLCMD mode is enabled in Server Management Studi (top menu). Toggle SQLCMD Mode on by clicking Query.

More information about the subject can be found here: MS Documentation

Answered by DanteTheSmith

Solution #5

GO statements are overwritten by temp tables, 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 attractive, but it gets the job done.

Answered by Remco Nonhebel

Post is based on https://stackoverflow.com/questions/937336/is-there-a-way-to-persist-a-variable-across-a-go