Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2003
    Posts
    2

    Unanswered: Creating a Databse on the Fly

    I wanted to Write A Stored Procedure which Will accepts the name of the "Database" as a parameter
    But when i am trying to do so i am getting error
    ------------------------------------------------------------------------------------
    create procedure create_DB
    @db_name as varchar(30)
    as
    create database @db_name
    -------------------------------------------------------------------------------
    The error is as Follows
    'Incorrect syntax near '@db_name'.'

    Pl help me in this regards

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

    Re: Creating a Databse on the Fly

    Originally posted by pankaj_bidwai
    I wanted to Write A Stored Procedure which Will accepts the name of the "Database" as a parameter
    But when i am trying to do so i am getting error
    ------------------------------------------------------------------------------------
    create procedure create_DB
    @db_name as varchar(30)
    as
    create database @db_name
    -------------------------------------------------------------------------------
    The error is as Follows
    'Incorrect syntax near '@db_name'.'

    Pl help me in this regards
    I guess you missed the brackets:

    create procedure create_DB
    (@db_name as varchar(30))
    as
    create database @db_name
    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
    Posts
    2

    Re: Creating a Databse on the Fly

    Hi
    No that is not the prob even if i use the brackets i will get an error
    If i give harcoded name instead of variable i don't get an error
    Originally posted by DoktorBlue
    I guess you missed the brackets:

    create procedure create_DB
    (@db_name as varchar(30))
    as
    create database @db_name

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    pankaj_bidwai, CREATE DATABASE does not accept a variable for the db name. Have you tried:

    Code:
    declare @db_name varchar(30)
    set @db_name = 'PSYTEST'
    execute('create database ' + @db_name)
    execute('drop database ' + @db_name)
    Last edited by Paul Young; 08-14-03 at 10:17.
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Aug 2003
    Posts
    4
    Remove the "AS" in the parameter list in the stored proc signature.

  6. #6
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Hu?? Please post corrected code demonstrating how this works.
    Paul Young
    (Knowledge is power! Get some!)

  7. #7
    Join Date
    Aug 2003
    Posts
    4
    Here you go....

    create procedure create_DB
    @db_name varchar(30)
    as

    exec('create database ' + @db_name)

    go

  8. #8
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    What happend to removing the "AS"?
    Paul Young
    (Knowledge is power! Get some!)

  9. #9
    Join Date
    Aug 2003
    Posts
    4
    Alas, that wasn't the problem after all. Using "as" in the parameter list is a technique that I've not seen before. I didn't think it was valid SQL. The real problem was the lack of dynamic SQL to build the CREATE DATABASE statement properly.

    I didn't read any of the other threads, so my reply may have been redundant.

Posting Permissions

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