Why doesn't this work?

This query changes the database:
PRINT DB_ID()
USE DATABASE2
PRINT DB_ID()

The output is:
7
13

This query does not however:

PRINT DB_ID()
DECLARE @DBName NVARCHAR(10)
SET @DBName = 'DATABASE2'
DECLARE @SQL NVARCHAR(20)
SET @SQL = 'USE ' + @DBName  
PRINT @SQL
EXEC(@SQL)
PRINT DB_ID()

The output is
7
USE DATABASE2
7


The question is why? I would like a decent explanation please or solution to the problem :)

Thanks!


LVL 1
craigdevAsked:
Who is Participating?
 
appariConnect With a Mentor Commented:
whatever you execute using EXEC is one batch and the variables declared, any set statements are local to that batch and are not visible outside once the execution of dynamic sql is over.

try this one

PRINT DB_ID()
DECLARE @DBName NVARCHAR(10)
SET @DBName = 'DATABASE2'
DECLARE @SQL NVARCHAR(20)
SET @SQL = 'USE ' + @DBName  + '; Print DB_ID()'
PRINT @SQL
EXEC(@SQL)
PRINT DB_ID()
0
 
craigdevAuthor Commented:
nice one thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.