Post reply

Create database fails when executing in SP

  • June 17, 2009 at 4:51 am

    #95429

    Hi All,

    Is it possible to create the SP in which new database is created by getting the database name at runtime?

    I have tried it, but Create database @databasename fails and gives incorrect syntax error.

    Code used by me to create SP is as follows:-

    Create Procedure DBCreationOnRuntime

    @DatabaseName Varchar(50)

    AS

    if exists(SELECT Name FROM master.Sys.databases WHERE name=@Databasename)

    print 'Database with same name already exists, please enter another name.'

    Else

    Create database @DatabaseName

    Print'Database with name "'+ Upper(@Databasename) + '" has been created successfully.'

    Every response is appreciated in this regard.

    Thanks,

    Ankur

  • June 17, 2009 at 4:56 am

    #1010758

    What is the error message that you're getting?


    Pradeep Singh

  • June 17, 2009 at 5:13 am

    #1010773

    I'm getting same error... it seems create database doens't accept variables.

    not even with sp_executesql.


    Pradeep Singh

  • June 17, 2009 at 5:16 am

    #1010778

    Thanks Pradeep,

    Error Message displayed on execution of code:

    Incorrect syntax near '@DatabaseName'.

    Is it a true that create database command doesnot accept variables in SS 2005?

    Ankur

  • June 17, 2009 at 5:41 am

    #1010804

    i tried googling but of no help. I guess it's not supported.


    Pradeep Singh

  • June 17, 2009 at 6:05 am

    #1010819

    Again Thanks Pradeep for Googling..

    Actually i have also tried the same but all in vain.

    Can anybody else guide in this regard?

    Thanks

    Ankur

  • June 17, 2009 at 6:10 am

    #1010820

    You cannot use sp_executesql and define the database as variable. You have to create a complete statement and use either sp_executesql or EXECUTE.

    This works fine on my system:

    CREATE PROCEDURE usp_CreateDatabase

    @Name NVARCHAR(128)

    AS

    DECLARE @sql NVARCHAR(MAX)

    SELECT @sql = N'CREATE DATABASE ' + QUOTENAME(@Name)

    EXECUTE (@sql)

    GO

    EXECUTE usp_CreateDatabase 'BA46EFB6-70C3-4E6D-8218-A546C7F8CBD4'

    GO

    DROP DATABASE [BA46EFB6-70C3-4E6D-8218-A546C7F8CBD4]

    GO

    DROP PROCEDURE usp_CreateDatabase

    Flo

  • June 17, 2009 at 6:23 am

    #1010833

    This is what i am looking for..

    It is indeed a great help..

    Thanks Florian 😀

  • June 17, 2009 at 6:29 am

    #1010840

    Glad we could help! 🙂

Viewing 9 posts - 1 through 8 (of 8 total)

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