Create a database in SQL Server 2005
Create a database in sql server 2005 but database name passed as parameter into a stored procedure
Software/Hardware used:
Software/Hardware used:
ASKED:
March 5, 2011 7:05 AM
UPDATED:
March 7, 2011 2:13 PM
Answer Wiki:
The CREATE DATABASE statement doesn't accept a parameter, so you'll need to use dynamic SQL.
<pre>CREATE PROCEDURE usp_CreateDatabase
@DatabaseName sysname
AS
DECLARE @sqlcmd NVARCHAR(4000)
SET @sqlcmd = 'CREATE DATABASE [' + @DatabaseName + ']'
EXEC (@sqlcmd)
GO</pre>
You'll also need to run the procedure as a login that has CREATE DATABASE rights. Either by logging into the instance with a login which has the rights or by using the EXECUTE AS statement within the procedure like is shown below.
<pre>CREATE PROCEDURE usp_CreateDatabase
@DatabaseName sysname
AS
DECLARE @sqlcmd NVARCHAR(4000)
SET @sqlcmd = 'CREATE DATABASE [' + @DatabaseName + ']'
EXECUTE AS LOGIN = 'SomeLogin'
EXEC (@sqlcmd)
REVOKE
GO</pre>
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
