Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 325
NitroxDM Avatar asked Jun 01 '09 23:06

NitroxDM


People also ask

What happens to a declared variable after the Go statement?

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.

Can you assign variables in the Declare statement?

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.

What is the scope of the variable in SQL?

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.

Which keyword is used when assigning a variable from a query?

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.


2 Answers

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 
like image 65
RBarryYoung Avatar answered Sep 22 '22 11:09

RBarryYoung


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.

like image 35
Guffa Avatar answered Sep 19 '22 11:09

Guffa