January 20, 2010 at 12:24 pm
Is it possible to change database context in a script without using dynamic SQL and instead using a variable and a "USE" statement? Something like:
declare @db varchar(10)
set @db = 'foo'
if @db = 'foo'
begin
USE [db_foo]
end
if @db = 'bar'
begin
USE [db_bar]
end
/*
some universal code here I want to execute against either database
*/
Well, I *know* this doesn't work because it's not actually setting the database based on my input variable. I try adding "GO" statements to actually execute the statements but then it yells at me about having to declare the variables, even though they are obviously already declared!
Is there no way to do this without building dynamic sql statements? I'd really rather do this, otherwise I'm just going to have to have 3 copies of the same set of code, one for each database. Any help would be greatly appreciated!
January 20, 2010 at 1:24 pm
Your code works fine for me. That is, if I change the DB names to be actual names in my SQL instance and fix the typo in your second IF block to use the keyword USE instead of USER.
By the way, GO is a batch separater so variables that you delcare prior to a GO statement are not visible to code beneath the GO.
January 20, 2010 at 1:40 pm
Oops, thanks for the heads up on the typos. Hmmmm, I'll have to try it again. When I ran as written, no matter what I set @db to it would always just set it to the last statement before executing the rest of the code. I.e., even if I set @db = 'foo' since 'bar' was after that it would seem to ignore the IF statement and set the active context to 'bar'.
But yeah, your description of the GO statements would make sense with what I'm seeing. Thanks, I'll try it again.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply