views:

164

answers:

4

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.

+3  A: 

You can just remove the GO statements, they are not needed.

However, you can't use a variable in a USE statement, it has to be the name of a database.

Guffa
+1  A: 

Not sure, if this helps

declare @s varchar(50)
set @s='Northwind'

declare @t nvarchar(100)
set @t = 'select * from ' + @s + '.[dbo].[Customers]'

execute sp_executesql @t
shahkalpesh
+3  A: 

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
RBarryYoung
A: 

you could use dynamic sql, just be aware of security risks with this (not 100% sure on the correct syntex)

declare @bob nvarchar(50)

declare @sql nvarchar(max)
set @bob='SweetDB'
set @sql = 'Use ' + @bob

execute sp_executesql @sql
DForck42