Changing to other Database by command

Question

Changing to other Database by command

anilyadav83

SSC Veteran

Points: 218
More actions
September 15, 2008 at 10:35 pm

#192832

How can i change to other database using command.
In stored procedure I am unable to write "USE DataBaseName"
Is there any other command?
declare @TargetDB as varchar(100)
declare @DBQry as varchar(100)
select @TargetDB = 'CL_T'
select @DBQry= 'use ' +@TargetDB
select @dbqry
exec(@dbqry)
I am running above script with no error but unsuccessful in getting what i wanted. Please help
[/url]

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

arjun.tewari Ten Centuries Points: 1013 More actions · Answer 1

anilyadav83 (9/15/2008)
How can i change to other database using command.
In stored procedure I am unable to write "USE DataBaseName"
Is there any other command?
declare @TargetDB as varchar(100)
declare @DBQry as varchar(100)
select @TargetDB = 'CL_T'
select @DBQry= 'use ' +@TargetDB
select @dbqry
exec(@dbqry)
I am running above script with no error but unsuccessful in getting what i wanted. Please help

The reason why you are unsuccessful is because dynamic SQLs are executed in a seperate SCOPE then the SCOPE of the batch calling them, and their SCOPE lasts only till the execution of dynamic SQL. Consider the following example for the clarification:

declare @TargetDB as varchar(100)

declare @DBQry as varchar(100)

select @TargetDB = 'CL_T'

select @DBQry= 'use ' +@TargetDB + ' select db_id()'

select @dbqry

exec(@dbqry)

select db_id()

Now coming to your problem. According to me, you can not switch the context this way, but you can have some workaround. Declare you @DBQry as VARCHAR(MAX), convert all your queries in dynamic SQL. Alternatively, you can use fully qualified name of the database objects.

ChiragNS One Orange Chip Points: 26137 More actions · Answer 2

If this is the scenario...you want execute a procedure named proc1 and the db that you want to execute the proc is dynamic, keep the proc in all the db's that you want.

If you are using a application to execute the procedure connect to the required db from the application and execute the proc.

If the connection to the required db is also from the database (not from application) use fully qualified names to connect to the database and execute the proc.

This secnario is feasible if the number of databases and the procedures to be managed is not very high. If you are using fully qualified names make sure proper user rights are given to the relevant users.

"Keep Trying"