[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 194
  • Last Modified:

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!


0
craigdev
Asked:
craigdev
1 Solution
 
appariCommented:
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

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now