I have a process which involves executing various commands between multiple databases - however, when I use dynamic SQL to change DB with 'use @var', then it doesn't actually change the database.
Executing this in [test_db]:
declare @currentDB varchar(max)
declare @sql varchar(max)
set @currentDB = DB_NAME()
set @sql = 'use [' + @currentDB +']'
use master
exec(@sql)
select DB_NAME()
Returns [Master] as the current database name - if I put use [test_db]
as a command, rather than dynamically, then it returns the correct name.
Is there a way to do this which will correctly switch between databases?