Need help creating dynamic "USE" statement... Thanks.

Question

Post reply

Need help creating dynamic "USE" statement... Thanks.

Jeff Moden

SSC Guru

Points: 1004313
More actions
April 26, 2004 at 4:17 pm

#111060

Howdy,
I know I've seen it somewhere on this forum and just can't find it again... somebody please tell me how to create a dynamic "USE" statement for inclusion as a parameter of a stored procedure?
Here's what I tried and it executes without error but does not change to the listed db.
DECLARE @MyDB nVARCHAR(1000)
DECLARE @MySQL nVARCHAR(1050)
SET @MyDB = N'Master'
SET @MySQL = N'USE '+ @MyDB + CHAR(13)
PRINT @MySql
EXEC sp_executesql @MySql
I'm running "Standard Edition 2000".
Thanks for the help...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 5 (of 5 total)

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

mssql_rules SSCrazy Points: 2783 More actions · Answer 1

Try:

DECLARE @MyDB nVARCHAR(1000) DECLARE @MySQL nVARCHAR(1050)
SET @MyDB = N'Master'
SET @MySQL = N'USE '+ @MyDB + ';'
PRINT @MySql EXEC @MySql

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 2

Got the same result... no error but no change. Thank you for trying.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mssql_rules SSCrazy Points: 2783 More actions · Answer 3

Unfortunately I do not have SQL on my home PC.

I remember trying this and, sure it worked:

DECLARE @MyDB nVARCHAR(1000) DECLARE @MySQL nVARCHAR(1050)
SET @MyDB = N'Master'
SET @MySQL = N'USE '+ @MyDB + ';' +'select db_name()'
PRINT @MySql EXEC (@MySql)

Karl Klingler SSCertifiable Points: 5869 More actions · Answer 4

Don't forget that the use-statement is only valid during the execution of the exec-command.

-> Add anything you want to be done to the string @MySQL

Best regards
karl

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Having fun with more dynamic dynamic sql

DECLARE @stmt NCHAR(1000)

SET @stmt = 'USE Frank_3 ' + CHAR(13) + 'DECLARE @stmt NCHAR(1000)' + CHAR(13) +

'SET @stmt = ''CREATE TABLE Second (col_a int)''' + CHAR (13) +

'EXEC sp_executesql @stmt'

EXEC sp_executesql @stmt

Do I need to add the reference to Erland Sommarskog again?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]