Switching between databases with dynamic SQL

Sure, there is a way - there's always a way...

If you declare variable and store in it the database and the procedure to run, you can exec it, with parameters.

Example

use tempdb;

select db_name();

declare @db sysname = 'master.sys.sp_executesql';

exec @db N'select db_name()';

set @db = 'msdb.sys.sp_executesql';

exec @db N'select db_name()';

It is trivial to then pass a query with parameters to be run in any database

declare @proc sysname, @sql nvarchar(max), @params nvarchar(max);

select 
  @proc = 'ssc.sys.sp_executesql'
, @sql = N'select top 10 name from sys.tables where name like @table order by name;'
, @params = N'@table sysname';

exec @proc @sql, @params, @table = 'Tally%'

I know this doesn't change database context in the main query, but wanted to demonstrate how you can conveniently work in another database in a safe parameterised way without too much bother.

Session-level changes made in a sub-process (i.e. EXEC / sp_executesql) go away when that sub-process ends. This covers USE and SET statements as well as any local temporary tables created in that sub-process. Creation of global temporary tables will survive the sub-process, and so will modifications made to local temporary tables that exist prior to the sub-process starting, and any changes to CONTEXT_INFO (I believe).

So no, you cannot dynamically change the current database. If you need to do something like this, you will need to execute any subsequent statements that rely upon the new database context also within that Dynamic SQL.