Andrew Ryan (7/9/2009)
I want to use vaariables with my USE commands.USE @DBName
You cannot specify the database name as variable for a USE call.
if I use this the database does not change but executes with no errors.
DECLARE @DBName char(3), @X as varchar(MAX)
SET @DBName = 'SBU'
set @X = 'use ' + @DBName +';'
EXEC(@X);
This works, but consider the execution context. The USE statement will be executed as own statement within your EXEC without affecting your outer context.
You can either use the syntax Terrance provided and add your statement into the variable, or enable SQLCMD mode (Menu -> Query -> SQLCMD Mode) within your script like this:
:setvar DatabaseName "Sandbox"
USE [$(DatabaseName)]
GO
SELECT COUNT(*) FROM Tally