Variable to select database in SQL query - Databases

TopAnswers Databases

Meta

Databases

TeX

Code Golf

APL

C++

.net

db<>fiddle

Java

*nix

PHP

PowerShell

Python

Rust

टेक्-मराठी

Typst

Web Client Dev

Web Server Dev

Variable to select database in SQL query

sql-server sql-server-2017 add tag

James Jenkins (imported from SE)

If I want use a variable in the `FROM` clause of a  SQL query I [need to use dynamic SQL.](https://stackoverflow.com/a/41250925/2112418) 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](https://docs.microsoft.com/en-us/sql/t-sql/language-elements/use-transact-sql?view=sql-server-ver15) 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)

Top Answer

James Jenkins (imported from SE)

Per the [comment by Tibor Karaszi](https://dba.stackexchange.com/questions/253195/variable-to-select-database-in-sql-query#comment499255_253195) 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](https://docs.microsoft.com/en-us/sql/t-sql/language-elements/execute-transact-sql?view=sql-server-ver15#using-execute-with-a-character-string)

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.](https://stackoverflow.com/a/1586588/2112418)  This can be a huge pain if there is a lot of code to review and test.

1 Answer