2

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)
3
  • 3
    It does change the database context, but that only apply tot he EXEC scope. Try adding the SELECT in the @sql variable and you'll see. Why doesn't it affect the outer scope? Only MS can answer why they decided to do it that way. You can open a Uservoice issue on this and see if MS decides to prioritize such a change, but I doubt... Commented Nov 13, 2019 at 17:31
  • @TiborKaraszi my final example seems to support your statement. Maybe your comment is the answer? Commented Nov 13, 2019 at 17:39
  • I didn't want to propose it as an answer since it doesn't really solve your problem, it just describes how things works: Not the way you want. :-) And, again, as for "why", only MS can tell... It is a design question. Commented Nov 13, 2019 at 19:21

1 Answer 1

3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.