Create database fails when executing in SP

• ankur_libra

  Hall of Fame

  Points: 3999

  More actions

  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

• ps.

  One Orange Chip

  Points: 29423

  More actions

  June 17, 2009 at 4:56 am

  #1010758

  What is the error message that you're getting?

  ---

  
  Pradeep Singh

• ps.

  One Orange Chip

  Points: 29423

  More actions

  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

• ankur_libra

  Hall of Fame

  Points: 3999

  More actions

  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

• ps.

  One Orange Chip

  Points: 29423

  More actions

  June 17, 2009 at 5:41 am

  #1010804

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

  ---

  
  Pradeep Singh

• ankur_libra

  Hall of Fame

  Points: 3999

  More actions

  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

• Florian Reischl

  SSC-Dedicated

  Points: 37301

  More actions

  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

• ankur_libra

  Hall of Fame

  Points: 3999

  More actions

  June 17, 2009 at 6:23 am

  #1010833

  This is what i am looking for..

  It is indeed a great help..

  Thanks Florian

• Florian Reischl

  SSC-Dedicated

  Points: 37301

  More actions

  June 17, 2009 at 6:29 am

  #1010840

  Glad we could help!