SQLServerCentral / SQL Server 2005 / T-SQL (SS2K5) / The USE command and variables / Latest Posts

RE: The USE command and variables

Florian Reischl — Thu, 09 Jul 2009 14:01:02 GMT

[quote][b]Andrew Ryan (7/9/2009)[/b][hr]I want to use vaariables with my USE commands.USE @DBName[/quote]You cannot specify the database name as variable for a USE call. [quote]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);[/quote]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:[code]:setvar DatabaseName "Sandbox"USE [$(DatabaseName)]GOSELECT COUNT(*) FROM Tally[/code]

RE: The USE command and variables

terrance.steadman — Thu, 09 Jul 2009 13:39:43 GMT

Greetings Andrew,I may be incorrect about this, but I suspect that the EXEC() operates like a batch. So, it is changing your DB focus, but when it finishes, so does your focus and it switches to the last active DB. You might need to wrap your extra code within the EXEC().[code]DECLARE @SQLString varchar(max)DECLARE @DBIn varchar(50)SET @DBIn = SET @SQLString = 'USE ' + @DBIn + '; SELECT * FROM MyTable'EXEC(@SQLString)[/code]Have a good day.Terry Steadman

The USE command and variables

Andrew Ryan — Thu, 09 Jul 2009 13:32:33 GMT

Sorry if this has been covered but as you might imagine searching for this has been problematic.I want to use vaariables with my USE commands.USE @DBNameGOif I use that I get an error that points to an arbitrary line in the code.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);I want to dynamically change which DB I am using. I could just be going about this the wrong way.