Inside Microsoft® SQL Server™ 2005

Environmental Settings

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 ...