As I mentioned in this chapter’s opening section, environmental settings (such as database context and SET options) that are set in a calling batch are in effect for a dynamic batch, but not the other way around. To demonstrate this aspect of environmental settings, the following code sets the database context of a calling batch to Northwind; it invokes a dynamic batch, which changes the database context to an input database name (pubs in this case); and finally, it outputs the database context of the outer batch after the dynamic batch is invoked:

USE Northwind;
DECLARE @db AS NVARCHAR(258);
SET @db = QUOTENAME(N'pubs'),
EXEC(N'USE ' + @db + ';'),
SELECT DB_NAME();

Because a change in database context in the inner batch has no effect on an outer batch, the output is Northwind and not pubs. On the other hand, environmental changes in the dynamic batch are in effect for the dynamic batch itself. Therefore, the following code returns the output pubs:

USE Northwind;
DECLARE @db AS NVARCHAR(258);
SET @db = QUOTENAME(N'pubs'),
EXEC(N'USE ' + @db + N' SELECT DB_NAME();'),

Similarly, such changes are in effect for levels inner to the dynamic batch, such as a nested level of dynamic SQL:

USE Northwind;
DECLARE @db AS NVARCHAR(258);
SET @db = QUOTENAME(N'pubs'),
EXEC(N'USE ' + @db + N'; EXEC(''SELECT DB_NAME();''),'),

The output of this code is also pubs.