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