CODEWITHSUNDEEP
sql-serversql-server-2008stored-procedures
Artur A
Artur A

Reputation: 9139

T-SQL USE satetement in condition

It seems that USE statement is compiled by MS SQL Server 2008R2.

For example,

if(1=0)
begin
   USE MyDB
end

The result will be in executing USE MyDB while ignoring IF statement.

How can it be changed to work with condition?

P.S. Also tried execute sp_executesql N'USE [MyDB]', it works with condition, but it does not change current context.

Upvotes: 4

Views: 3902

Answers (3)

granadaCoder
granadaCoder

Reputation: 27852

When you need a conditional "Use MyDB", I would look at sqlcmd and variables.

Use $(DatabaseName) 
GO

That's the only way I have found to get "dynamic" Use MyDatabase names.

Reference article:

technet.microsoft.com/en-us/library/ms188714.aspx

https://learn.microsoft.com/en-us/sql/tools/sqlcmd/sqlcmd-use-scripting-variables?view=sql-server-ver16

Upvotes: 3

Alejandro
Alejandro

Reputation: 7813

Indeed the correct way to change databases at runtime is precisely dynamic SQL, which is not compiled or validated until it's actually executed. Your sample, failing code involving sp_executesql in fact DOES change the context, but the gotcha is that the change lives only for the dynamic SQL batch, not on the calling code. To actually see and use that database, further code must be put inside the dynamic SQL block, and executed at once by EXEC/sp_executesql:

USE master ;
EXEC (N'USE tempdb ; PRINT db_name() ;') ;
PRINT db_name() ;

If you absolutely need to do that dynamic database switch, I think you can refactor the script to make it only build a string containing the commands to run, and that 1=0 condition only prepend a proper USE statement to choose a database to work with.

Upvotes: 2

aleroot
aleroot

Reputation: 72636

Try with Dynamic SQL in this way : 

EXEC ('USE [MyDB];')

Upvotes: -1

Related Questions