Is it possible to pass a parameter in the Use command?

Question

Is it possible to pass a parameter in the Use command?

Dionisio T Manzo

SSC Enthusiast

Points: 173
More actions
July 21, 2004 at 8:19 pm

#152235

Hi,
I am trying to passed an a parameter in the Use command in the Query Analyzer. (SQL Statement shown below)
DECLARE @DBName NVARCHAR(10)
DECLARE @StrSQL NVARCHAR(20)
SET @DBName='SIVAT'
SET @StrSQL = 'USE ' + LTRIM(@DBName)
EXEC SP_EXECUTESQL @StrSQL
GO

Viewing 8 posts - 1 through 7 (of 7 total)

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

Phil Parkin SSC Guru Points: 246986 More actions · Answer 1

The statement works, but the change in database context lasts only while the SQL statement executes. Here's a quote from BOL:

"If the executed string has a USE statement that changes the database context, the change to the database context only lasts until sp_executesql or the EXECUTE statement completes."

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Dionisio T Manzo SSC Enthusiast Points: 173 More actions · Answer 2

Phil,

Could you please explain further? I tried to put a select statement after the GO command. But still it does not change the database.

Phil Parkin SSC Guru Points: 246986 More actions · Answer 3

OK, what I think happens is that the database context changes only for the duration of the sp_executesql command and then reverts immediately afterwards.

So ... if you expand your definition of @strSQL to include the select statement as well:

SET @StrSQL = 'USE ' + LTRIM(@DBName)

set @strsql = @strsql + ' select top 10 * from tablename'

EXEC SP_EXECUTESQL @StrSQL

It should work. Note also that you will need to change your declaration section to provide more space in @strSQL for the text of the commands you want to issue.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Dionisio T Manzo SSC Enthusiast Points: 173 More actions · Answer 4

Phil,

Your code work but the DB shown in the query analyzer doesn't change.

Phil Parkin SSC Guru Points: 246986 More actions · Answer 5

That's because it hasn't changed. It changed only for the duration of execution of the sp_executesql command. I could not find a way of making the db context change permanently using a variable db name. Perhaps someone else here knows of a way.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Naren Bhat SSC Veteran Points: 224 More actions · Answer 6

If the only point is to change the database, would it make sense to use always the four part name for the table. <server><db><owner><table>.

Thanks

Dionisio T Manzo SSC Enthusiast Points: 173 More actions · Answer 7

Naren,

Your right. And there are lots of way to do it but I just want to know if it could be possible to do it by passing a parameter on this type of scenario. And Phil done it right.