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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply