Dynamic USE <YourDBNAME>

I know all about using dynamic queries and building queries on the fly, but I have a problem with trying to dynamically Use a database.

I have a script (or at least I'm trying to have a script) that will create (install) a database for me, then create the tables in the database.

I'm using a variable for the database name because the same database may be used for different projects on the same server. I only wanted to set the name of the database once.

I was hoping that after I created the database that I could somehow tell the script to start using that database.

Use @.mydbname is incorrect and if I use the stored proc to execute dynamically, it is not maintained after executing the proc. (Some silly thing about scope).

Does anyone have any ideas I could use?

ThanksWhere is your script stored? As a file, or as a stored procedure, or in a table?

If you are using an interface, like VB, then the simplest solution is to have the interface execute one script to create the database, switch to the new database, and then execute a second script to create the objects.

If you are running a stored procedure or an sql file, you could package the entire code as dynamic SQL and issue the USE statement in your code. sp_Execute can handle multiple statements, including USE. It is limited in size, I believe, so you would probably have to break your code up by object and inclued the USE statement in each section.

This question has come up before, so you could browse previous answers, but I have to say I have never seen a multi-database design like this that didn't result in an ongoing mess of version discrepancies, data duplication, and fragile patchworks of DTS packages to keep the whole thing moving. You'll probably tell me that this is not an option, but build scalability into your database design from day one and you will avoid a multitude of problems.|||Originally posted by rmillman
I know all about using dynamic queries and building queries on the fly, but I have a problem with trying to dynamically Use a database.

Thanks

I guess, the solution you are looking for is to use dynamic SQL. You will have to use the EXECUTE statement. Paste this code in the query analyzer, and run it:

USE master
GO

DECLARE @.DB AS VarChar(50)
SET @.DB = 'YourNewDB'
EXECUTE('CREATE DATABASE ' + @.DB)
EXECUTE('CREATE TABLE ' + @.DB + '.dbo.YourFirstNewTable(YourFirstCol int)')
-- add your other stuff
GO

Is this you are looking for?|||Originally posted by rmillman
I have a script [...] that will create (install) a database for me, then create the tables in the database.


Just checking: have you tried the -d <dbname> switch in isql ? Just like your sentence above, your batch file would have two parts. First create the database, then swith to it and use it. I would structure the BAT file as follows:

set DBNAME=mydb
isql -Q "create database .%DBNAME% ..." -S ... -U ...

isql -S ... -U ... -d %DBNAME% -i script.sql

The above executes the create database statement on the command line, so you caan substitute the variable. In the second invocation, the "script.sql" is where you create all your objects. This is pure command-line mode, so it is easily repeatable.