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.
Variables declared before the GO statement are not accessible after the GO statement. Basically SSMS sends the first batch (i.e. Batch 1) of statements to the SQL Engine first, once its execution is over it sends the second batch of statements (i.e. Batch 2) after the GO statement to the SQL Engine for execution.
Variables in SQL procedures are defined by using the DECLARE statement. Values can be assigned to variables using the SET statement or the SELECT INTO statement or as a default value when the variable is declared. Literals, expressions, the result of a query, and special register values can be assigned to variables.
The scope of a variable is the range of Transact-SQL statements that can reference the variable. The scope of a variable lasts from the point it is declared until the end of the batch or stored procedure in which it is declared.
In below snapshot, SELECT statement is used to assign value to a variable from a select query. The SELECT statement assigns last value from the result set to the variable if the select query returns more than one result set.
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
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With