Creating a database on the fly using a T-SQL stored procedure

• Gregory D. Harris

  Valued Member

  Points: 70

  More actions

  June 24, 2005 at 12:40 pm

  #186282

  I'm having problems creating a T-SQL stored procedure that creates a new SQL Server 2000 database on the fly from my ASP.NET Web application. I'm trying to pass in a parameter (the name of the database) to the procedure and have my CREATE DABASE DATABASENAME statement receive the value of the parameter. Here is my code:

  CREATE PROCEDURE DBO.CREATE DATABASE

  @DBNAME VARCHAR (50)

  AS

  CREATE DATABASE @DBNAME

  GO

  The procedure works If I hard code the name of the database but not when I pass in the parameter to become the name of the database. Anybody's help is greatly appreciated. Thanks!

   

• DSP-120308

  Hall of Fame

  Points: 3959

  More actions

  June 24, 2005 at 12:48 pm

  #568369

  Try something like this:

  Create Procedure dbo.Create_Database

  @DBNAME VARCHAR(50)

  AS

  DECLARE @SQL VARCHAR(100)

  SELECT @SQL = 'CREATE DATABASE ' + @DBNAME

  EXEC(@SQL)

  RETURN

• Gregory D. Harris

  Valued Member

  Points: 70

  More actions

  June 24, 2005 at 12:55 pm

  #568372

  Thanks for the quick response helping me get my SQL Server stored procedure working!