January 17, 2014 at 4:11 am
Hi,
I was wondering what is the best way to change context within a DB.
For example you could have a simple script which restores a DB you might have something like this in it.
How could you get this to work in an SP?
IF @value = 'newdb1'
USE [newdb1]
ELSE IF @value = 'newdb2'
USE [newdb2]
ELSE IF @value = 'newdb3'
USE [newdb3]
exec sp_change_users_login 'Update_One', 'user1', 'user1'
January 17, 2014 at 6:04 am
It is not allowed to use the USE statement inside a stored procedure. The only solution to do this is by using dynamic SQL.
create procedure usp_change_db_context
as
begin
declare @dbname sysname
declare @sql_use_db nvarchar(200)
declare @sql_command nvarchar(800)
declare @sql_statement nvarchar(1000)
set @dbname = 'AdventureWorksLT'
set @sql_use_db = 'USE [' + @dbname + '];'
set @sql_command = 'EXEC sp_executesql N''select db_name();'''
set @sql_statement = @sql_use_db + @sql_command
print @sql_statement
exec (@sql_statement)
set @dbname = 'AdventureWorksLT2008R2'
set @sql_use_db = 'USE [' + @dbname + '];'
set @sql_statement = @sql_use_db + @sql_command
print @sql_statement
exec (@sql_statement)
end
go
exec usp_change_db_context
January 17, 2014 at 6:33 am
Edward-445599 (1/17/2014)
Hi,I was wondering what is the best way to change context within a DB.
For example you could have a simple script which restores a DB you might have something like this in it.
i kind of doubt there's any need to change a database conteext anyway; you can refer to any object via three part names directly:
newdb1.dbo.Table1 etc;
your post seems to imply you want to restore a database, is that right?
if you explain what your objective is, i'm sure we can offer some peer reviewed examples on how to accomplish it.
Lowell
January 20, 2014 at 2:01 am
thank you
January 20, 2014 at 2:02 am
Hi Lowell ,
Indeed your correct , restoring from a template DB and restoring over existing DB's.
Many thanks
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy