0

I've got a really simple dynamic using clause that doesn't work. In fact it causes the script to silently fail and so I'm guessing there is a security issue as SQL Server isn't reporting what is going on and whenever that happens its security.

declare @DatabaseName varchar(100)
declare @query1 nvarchar(500)
select @DatabaseName = 'mydb'   
set @query1 = 'Use ' + @DatabaseName + ''
exec (@query1)

Deems simple, but it won't use 'mydb'.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Tom McDonald
  • 1,532
  • 2
  • 18
  • 37
  • note, I'm using sql 2019 and I have a friend who is able to get the above to work on sql 2019. – Tom McDonald Mar 09 '21 at 19:43
  • Have you tried running this in SQL management Studio to test it out? All your query is doing is changing the DB its using, its not doing anything else? You can change the EXEC to PRINT in Management Studio and see what the query looks like and run that in Management Studio as well. I run your code in SSMS and it runs fine. – Brad Mar 09 '21 at 19:43
  • 2
    It does not fail - it just does not do what you (likely) want. You are changing the current database for the (very short) duration of the EXEC statement. Without knowing what you are trying to do, that is all one can say at this point. Perhaps you can glean some ideas from [this dba post](https://dba.stackexchange.com/questions/118729/switching-between-databases-with-dynamic-sql/118839) – SMor Mar 09 '21 at 19:50
  • USE is usually used in context with "GO". GO is a keyword only for SSMS, it is not a T-SQL keyword, so what you're trying to do in this context isn't possible. What IS possible is to use 3 part names for tables within your query (Select * from dbname.schema.table), and built your SQL string appropriately for a cross database query. – Jeremy Mar 09 '21 at 19:56
  • What is the expected outcome? I think SSMS should not switch to that DB, because EXEC has its own scope. What happens when you run this: `declare @DatabaseName varchar(100) declare @query1 nvarchar(500) select @DatabaseName = 'mydb' set @query1 = 'Use ' + @DatabaseName + ' select DB_NAME()' select DB_NAME() exec (@query1) select DB_NAME()`? In my case SSMS did not switch to `mydb`, but from EXEC it did returned that, from the other two it returned the outer query's DB. – Dávid Laczkó Mar 09 '21 at 19:57
  • 5
    The `USE` is only good for the inner scope of the EXEC batch. The database context returns back to the original db after execution. – Dan Guzman Mar 09 '21 at 20:03
  • What are you *really* trying to achieve? What we have here is clearly an [XY Problem](http://xyproblem.info). – Thom A Mar 09 '21 at 20:07
  • @Jeremy, No - USE is a valid TSQL keyword - https://learn.microsoft.com/en-us/sql/t-sql/language-elements/use-transact-sql – Stu Mar 09 '21 at 21:23

2 Answers2

0

I think you'll find it's working just fine.

use master
go
select Db_Id()

declare @DatabaseName sysname = 'mydb', @query1 nvarchar(500)
set @query1 = 'Use ' + @DatabaseName + '; select Db_Id()'
exec (@query1)

select Db_Id()
Stu
  • 30,392
  • 6
  • 14
  • 33
  • But what question is it answering? I put the same thing into a comment, but first asking what the expected outcome was. What does "working" mean? What does "fine" mean? – Dávid Laczkó Mar 09 '21 at 21:47
  • @DávidLaczkó I didn't see your post (I have now I've expanded the list) - you are of course correct and imply the same thing. My intention, the same as yours, is to show the OP there is nothing wrong with the dynamic sql he has, it's just the expectation is wrong, and that additional t-sql will be in the context of the expected database. The question is why will it not USE the database, the answer is.. it is, you just need the evidence. – Stu Mar 09 '21 at 21:54
0

@DanGuzman your comment is correct. What I'm asking apparently can't be done. As soon as I leave the scope of the exec the db isn't 'mydb' thats what I was trying to do, so that all statements after the exec would run using mydb. The statement was in a loop and I wanted to loop through all the dbs on the instance which might be possible if I stuff all the dynamic sql into the exec, that would be a mess.

Tom McDonald
  • 1,532
  • 2
  • 18
  • 37