Conference amcucs::ms-sqlsvr

351.0. "Variables in CREATE DATABASE?" by MSE1::MASTRANGELO () Thu Mar 20 1997 12:55

                

    I did a search and didn't find anything that would help so here's my
    problem:

    I would like to create some databases using a stored procedure and
    would like to pass in the names of the database, data device and log
    device as parameters.  Using variables to create the devices seems to
    work fine but using variables with the CREATE DATABASE statement
    doesn't.  Here's an example:

    declare @dbname varchar(20)
    declare @datadevice varchar(20)
    declare @logdevice varchar(20)

    select @dbname = "pubs1"
    select @datadevice = "pubs1data"
    select @logdevice = "pubs1log"

    (create data and log devices ok)

    CREATE DATABASE @dbname	/* doesn't work */
    ON @datadevice = 10
    LOG ON @logdevice = 5

    I have tried surrounding the variable names with single and double quotes
    but I still get a syntax error.

    Msg 170, Level 15, State 1
    Line xx: Incorrect syntax near '@datadevice'.
  
    (xx is the actual line in my code)

    What should the syntax be when using variables in the CREATE DATABASE
    statement?
    
    SQL Server Books Online didn't have an example.  Any insight is greatly
    appreciated.