December 23, 2024 at 8:43 pm
I am not understanding why this command completes "successfully" and it seems correct to me. However, the focus of the session does not switch to the database specified in the USE clause. Is there something I am missing?
I am trying to make my script able to run on any database it finds, and change the database;
DECLARE @sql nvarchar(30)
DECLARE @var1 nvarchar(30)
SET @var1 = 'Database1'
SET @sql = 'USE ' + @var1
EXECUTE sp_executesql @sql --this should set focus on Database1
It completes, but the focus is still on another database (in the drop down window in SSMS). I also run a test select on one of the tables in the database and it can't find it. What am I missing from this? Steve
December 23, 2024 at 9:52 pm
The change in database focus only occurs within the scope of the dynamic sql session. Your "outer" session is still using whatever database you were using prior to executing sp_executesql.
December 24, 2024 at 12:38 am
If you read the help for sp_ExecuteSQL, you can see that this point is explicitly mentioned:
sp_executesql has the same behavior as EXECUTE regarding batches, the scope of names, and database context. The Transact-SQL statement or batch in the sp_executesql @stmt parameter isn't compiled until the sp_executesql statement is executed. The contents of @stmt are then compiled and executed as an execution plan separate from the execution plan of the batch that called sp_executesql. The sp_executesql batch can't reference variables declared in the batch that calls sp_executesql. Local cursors or variables in the sp_executesql batch aren't visible to the batch that calls sp_executesql. Changes in database context last only to the end of the sp_executesql statement.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply