CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Corovei Andrei
Corovei Andrei

Reputation: 1668

differences between Use and DB.sp_execute

I have realized that there are 2 ways of running a dynamic string on a different database:

the first one is the following:

declare @sql nvarchar(max)
set @sql = 'select 1'
exec MyDatabase.sp_execute @sql

the second one would be:

declare @sql nvarchar(max)
set @sql = 'Use MyDatabase
            select 1'
exec sp_execute @sql

Can anyone point the differences between the 2 approaches? If one is better than the other and why?

Upvotes: 1

Views: 87

Answers (1)

Eric
Eric

Reputation: 95153

Use set's the database context for the current session, while in your exec statement, you're telling it what database to use for only the statement. After the exec statement, your session's database context won't have change to what you ran.

So, in the case of your example, there's no functional difference. In the more general sense, there is a rather large one.

You can also do this:

select * from MyDatabase.dbo.MyTable

That will pull data from another database than your context. You can use this to join data between databases, as well, just like you do normal joins.

Upvotes: 2

Related Questions