Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003

    Unanswered: USE command question

    I am writing a script that sets up a database with default values but I would like to keep it an external script rather than make it a stored procedure. The issue I am having is that I would like the script runner to set values at only one section of the Trans-SQL script at the top like so:

    --Declare the vars
    DECLARE @DBName varchar(50)

    --Set the vars
    --The user changes this section only
    SET @DBName='myDB'

    Here is where I run into trouble. I want to use the USE command to now change to the database named in the @DBName var but USE does not accept variables as the name of the database to change to. This will not work:

    USE @DBName

    I get the following error:

    Incorrect syntax near '@DBName'.

    I have looked for a stored procedure to change the current database but I have not found one. Help! Does anyone know how to do this in Trans-SQL? Thanks in advance.
    Last edited by Charlie Babbage; 08-30-03 at 17:28.

  2. #2
    Join Date
    Aug 2003
    Delft, The Netherlands (EU)

    Re: USE command question

    You probably not searched this forum; see this thread for an answer.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  3. #3
    Join Date
    Aug 2003

    Still having trouble

    I read the thread and tried the example code below:

    DECLARE @DB AS VarChar(50)
    SET @DB = 'YourNewDB'

    That code does indeed create a database named YourNewDB but if I modify the code as such:

    DECLARE @DB AS VarChar(50)
    SET @DB = 'myDB'
    EXECUTE('USE ' + @DB)

    the current database does not change to myDB. I know this not only because my code afterward fails but if I simply do a SELECT db_name() after the dynamic USE I get whatever is set in Query Analyzer. Is there something special about the use command that I don't know? Am I doing it right?

  4. #4
    Join Date
    Oct 2001
    use affects the current batch so will change the database context in your dynamic sql command but it will revert on completion.

    You can put all your commands into dynamic sql with the use command (or many commands each withy a use).
    Or you can execute

    exec mydb..sp_executesql 'sql' - this will execute in the context of the database - but you will need to enclose the whole lot as dynamic sql so you won't gain anything.

    Another option is to create a text file with all the commands (a use at the top or the database as a parameter for the command) and execute it using osql. See
    for an example of creatina text file for use with ftp - osql can be used in a very similar way.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts