Create a data base in sql server 2005 using a stored procedure but database name passed as a parameter
2
-
At least attempt it yourself firstPhil Helix– Phil Helix2011-03-05 07:01:27 +00:00Commented Mar 5, 2011 at 7:01
-
1You could use dynamic SQL to do this by forming the CREATE DATABASE SQL statement using the @databaseName stored procedure parameter (where @databaseName is an example of a potential procedure parameter)bitxwise– bitxwise2011-03-05 07:04:55 +00:00Commented Mar 5, 2011 at 7:04
Add a comment
|
3 Answers
Below query block might be helpful.
DECLARE @Query VARCHAR(MAX)=''
DECLARE @DbName VARCHAR(400) = 'Db1'
DECLARE @DbFilePath VARCHAR(400) = 'E:\Database\'
SET @Query = @Query + 'CREATE DATABASE '+@DbName +' ON PRIMARY '
SET @Query = @Query + '( NAME = '''+@DbName +''', FILENAME = '''+@DbFilePath+@DbName +'.mdf'' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) '
SET @Query = @Query + ' LOG ON '
SET @Query = @Query + '( NAME = '''+@DbName +'_log'', FILENAME = '''+@DbFilePath+@DbName +'_log.ldf'' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)'
print @query
exec(@query)
Comments
As bitxwise has said, you will need dynamic SQL
create proc createdb @dbname sysname
as
declare @sql nvarchar(max)
set @sql = 'create database ' + QUOTENAME(@dbname)
exec (@sql)
That's just the most basic CREATE DATABASE statement. Normally in a production environment, you wouldn't want to have such a proc anyway, and even if you do - you would specify growth, initial size, location etc.
But here's the reference: http://msdn.microsoft.com/en-us/library/ms176061(v=SQL.90).aspx.
Comments
Use EXEC sp_executesql:
DECLARE @Query NVARCHAR(MAX)
set @Query=''
DECLARE @DbName NVARCHAR(max) = 'Db1'
DECLARE @DbFilePath NVARCHAR(max) = N'E:\Database\'
SET @Query = @Query + 'CREATE DATABASE '+@DbName +' ON PRIMARY '
SET @Query = @Query + '( NAME = '''+@DbName +''', FILENAME = '''+@DbFilePath+@DbName +'.mdf'' , SIZE = 12288KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) '
SET @Query = @Query + ' LOG ON '
SET @Query = @Query + '( NAME = '''+@DbName +'_log'', FILENAME = '''+@DbFilePath+@DbName +'_log.ldf'' , SIZE = 32448KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)'
print @query
EXEC sp_executesql @query
