Variable to select database in SQL query

Question

If I want use a variable in the FROM clause of a SQL query I need to use dynamic SQL. Which might be a pain if the query is long and complex.

There are few different ways of running a query from a database you are not currently connected to. The two methods I use most often are these..

Select * from msdb..sysjobs_view

or

USE MSDB
Select * from sysjobs_view

If you start on the master database both of the above will give you the same results.

When I put these both into dynamic SQL one works and the other does not

Starting from master, this works

DECLARE @DBN SYSNAME 
SET @DBN = 'msdb'
DECLARE @Sql NVARCHAR(MAX)
SET @Sql = N'Select * from ' + @DBN + '..sysjobs_view'

EXEC (@Sql)

But this does not

DECLARE @DBN SYSNAME 
SET @DBN = 'msdb'
DECLARE @Sql NVARCHAR(MAX)
SET @Sql = N'USE ' + @DBN 

EXEC (@Sql)
Select * from sysjobs_view

I get the error

Msg 156, Level 15, State 1, Line 14 Incorrect syntax near the keyword 'EXEC'.

My reason for this: One line of dynamic SQL in big complex query is going to be less painful to code than trying to turn the whole query into dynamic SQL. My question here is just about why its not working with the USE clause.

Why can't I use dynamic SQL with the USE clause to change the database context?

NOTE: The below does work and would be easy to code. But my question about why remains.

DECLARE @DBN SYSNAME 
SET @DBN = 'msdb'
DECLARE @Sql NVARCHAR(MAX)
SET @Sql = N'USE ' + @DBN  +'
Select * from sysjobs_view
'

EXEC (@Sql)

score 3 · Accepted Answer · edited Jun 15 '20 at 09:05

Per the comment by Tibor Karaszi the change only applies "to the EXEC scope" This is validated by the Microsoft statement below.

In SQL Server, the varchar(max) and nvarchar(max) data types can be specified that allow for character strings to be up to 2 gigabytes of data.

Changes in database context last only until the end of the EXECUTE statement. Source

The easiest work around is probably, the final example in the question. Adding a couple of lines at the beginning and end of your code can change the context for just a single execution. The rest of your code stays on whatever the context was before the `EXEC'

DECLARE @DBN SYSNAME 
SET @DBN = 'msdb'
DECLARE @Sql NVARCHAR(MAX)
SET @Sql = N'USE ' + @DBN  +' -- Add this
Select * from sysjobs_view
-- Up to 2GB of code here
' --And add this
EXEC (@Sql)

There are limitations to using this dynamic SQL solution If there are single quotes in your 2GB of code, they have to be escaped by doubling the single quote. This can be a huge pain if there is a lot of code to review and test.

Variable to select database in SQL query

1 Answers1