How to create New database using store procedure

• deba_20032004

  Default port

  Points: 1476

  More actions

  December 20, 2009 at 11:53 pm

  #96782

  Dear Sir,

  I am tring to create database use store procedure through input parameter. but it give syntax error

  I can't under standad why it is giving syntax erro.

  This is my code below

  CREATE PROCEDURE sh_CreateDataBase

  @tmpDb as Char(15),--input parameter for creating database

  @tmpPath as VarChar(50)--input parameter for giving path or folder where database is created

  As

  CREATE DATABASE @tmpDb

  ON PRIMARY

  (NAME=@tmpDb + '_dat',

  FILENAME=@tmpPath+'\'+tmpDb+'.mdf',

  SIZE=10MB,

  MAXSIZE=100MB,

  FILEGROWTH=10%)

  LOG ON

  (NAME=@tmbpDb+'_log',

  FILENAME=@tmpPath+'\'+tmpDb+'.ldf',

  SIZE=5MB,

  MAXSIZE=20MB,

  FILEGROWTH=5%)

  Server: Msg 170, Level 15, State 1, Procedure sh_CreateDataBase, Line 6

  Line 6: Incorrect syntax near '@tmpDb'.

• Sunny-138471

  Ten Centuries

  Points: 1162

  More actions

  December 21, 2009 at 12:13 am

  #1094595

  CREATE DATABASE statement does not accept variables. You can try dynamic query instead. Check these links:

  http://www.umachandar.com/technical/sql6x70scripts/UtilitySPs(6x)/Main9.htm

  http://www.databasejournal.com/scripts/article.php/1546441/spcreatedatabaseSQL.htm

  Satish